Questions about using in-cell functions/formulas in spreadsheet software.

**1**

vote

**0**answers

573 views

### Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...

**0**

votes

**1**answer

130 views

### How can I do this in spreadsheets?

Let's say you have a table like this:
| A | B | C |
-------------------------------------
1| sex | age | answer q1 |
-------------------------------------
2| ...

**0**

votes

**1**answer

320 views

### Turning students’ marks into grades with excel

I have a sheet contain grade boundaries, For example, the first row says ‘A* | 100 | 90’, the next row is ‘A | 90 | 80 ’, the next ‘B | 80 | 70’ and so on. Another sheet contains a list of student in ...

**1**

vote

**1**answer

357 views

### Get the row and column for a cell in a named range?

Given a cell that exists in a named range, what's the cleanest way to get the row and column numbers for that cell inside the named range? In other words, I want the row and column number relative to ...

**0**

votes

**2**answers

4k views

### How to split comma-separated values by formula, not by script

In A1 I have the following string.
"831 days , 12673 total edits, 12286 approved, 5974 reviews"
I want a formula (not script) that can take this string and return the following in B1:E1
B1=831 ...

**2**

votes

**1**answer

4k views

### Conditional Formatting in excel based on another column

I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are ...

**0**

votes

**1**answer

17 views

### Calculating non-duplicates

I have below data in three columns.
A B C
07002 72J217.4 72J217
07002 72J229.5 72J129
07002 72J229.6 72J129
07002 72J229.7 72J129
07006 61E001.5 61E001
...

**1**

vote

**1**answer

144 views

### Need a Excel script to identify email addresses that contain a different variation of the first name of the contact

I have an Excel spreadsheet with 50k+ rows of data, where we are trying to identify within the rows of data where the email address has no match of the first name, etc.
We are basically trying to ...

**0**

votes

**1**answer

96 views

### Excel - pulling data from certain sheet of different workbook

I am trying to pull pricing data from a larger workbook (masterprice.xlsx) for a new basic "price quote" workbook. The large workbook has a different sheet for each model name. In my new workbook, I ...

**0**

votes

**0**answers

63 views

### Displaying some cells data depending on selected text from other sheet

I have an excel sheet which looks similar to below and with some more columns.
Area Branch Empolyees Products Shelfs
Area1 Area1 Branch1 10 Product 1 10
Area1 Area1 ...

**1**

vote

**0**answers

18 views

### transposing a row with formulae & values both in several cells to another sheet in same file Excel [duplicate]

Consider the data arranged as follows in my Sheet1. I want the fifth row to be transposed in the Sheet2.
When I reference the first item from B5 in Sheet2, and then drag the fill-handle(?) ...

**0**

votes

**3**answers

121 views

### Put two Excel formulas together

I have some particular data in sheet1 and some other data in sheet2.
I need to compare a specific value(Var1) from sheet1 and return a specific value from the sheet2, and return an output for the ...

**2**

votes

**3**answers

242 views

### How to make excel return a date from a column corresponding to the last 'YES' in a different column

I'm trying to develop a spreadsheet tool to help track the training currencies of professional aircrew. In order to be considered 'current' in all training, aviators must accomplish a variety of ...

**1**

vote

**2**answers

51 views

### summing values that match text critera

I have a need to create an Excel Sheet that can compare a column of employees' names to a second sheet that has a certain number of monetary figures, as such:
list of specific employees who's profit ...

**2**

votes

**1**answer

56 views

### What is the cleanest way to write an array formula to facilitate the addition of more rows?

I have a spreadsheet with several columns that use array formulas, specifically referencing other columns of data. I started off using the A$1:A$100 syntax (if 100 was the last row of data in the ...

**0**

votes

**1**answer

2k views

### Excel 2007 - Using combination of Countif and Indirect

I have numeric data in column P in 50 different sheets ( each sheet has different person name). I have one summary Sheet which has names of all the worksheets in column A. In summary sheet column B ...

**0**

votes

**0**answers

66 views

### Mail Merge | Excel transpose or any other solution

i have an excel with 3 columns: domains, clients, email clients
each client can have more than one domain.
I want to mail merge all my clients to make a change in the dns. but i don't want to send 5 ...

**0**

votes

**2**answers

61 views

### Excel 2007 - refering value from other worksheet in another function in excel

I have two worksheets. I have data in Sheet2 in column A for which i want to use average function. In sheet1 i have values in only two cells. B1 cell has value 5 and B2 cell has value 15. Using these ...

**0**

votes

**1**answer

32 views

### How do I replace two or more instances of a character in an Excel string?

For example, what function or combination of functions would I use to convert string 1, "this_is_a_string" to string 2, "this is a string"?

**0**

votes

**1**answer

110 views

### Excel formula for increasing the row number in excel 2007

Please. The formula already posted for increasing the row number while dragging across
columns.(=OFFSET($A$1,COLUMN()-1,)
Want to drag down formula in rows, increasing the column letter, using same ...

**0**

votes

**2**answers

44 views

### “#REF!” appears in cell after referred-to cells are pasted in

On Office 2007's Excel on Win7, I have a file with a formula like AVERAGE(A29:A33) in cell A34. I then copy four vertically-adjacent number-containing cells and paste them into cells A29:A33. At ...

**0**

votes

**2**answers

72 views

### How do I locate two values in excel found on the same row?

I've got an index with multiple sheets full of names, sections, townships, ranges, dates, books, pages... and wanted a formula to locate two cells matching on the same row. For example: Column 'D' ...

**0**

votes

**1**answer

111 views

### Subtracting hours from time

I am trying to subtract hours from a time, but it's giving me strange results
A1 = 06:00:00 (manual entry, hours to deduct)
B1 = 00:30:00 (=C1-A1, start time)
C1 = 06:30:00 (manual entry, end time)
...

**0**

votes

**0**answers

80 views

### Excel Forumla for SUMPRODUCT to exclude rows/cols

I am rather new to using Excel. How can I take a formula like this
=SUMPRODUCT(B3:B39, A3:A39)
and have it exclude specific rows that I am using as headers/dividers. But seem to break the formula ...

**0**

votes

**1**answer

21 views

### How to delete and retain a particular value in a cell based on the value of a certain cell?

How to make a vb code to delete the contents of Column B when i entered a specific value in column A?
Example: If cell A1=apple, cell B1 should delete whatever value it have. But if cell A1=grape, ...

**-1**

votes

**2**answers

1k views

### How Do You Stop Auto Formatting to a Cell I'm Using as a Barcode Number [duplicate]

I am having a little bit of a problem with Excel. I am typing into a cell a barcode number for my products but every time I leave the cell it automatically formats the cell as below.
Number I want to ...

**1**

vote

**2**answers

182 views

### Excel: Check for specific value in a matching row

I want to check which products (listed in rows) are produced by which company (listed in column headings) in order to fill a table like this
Company A Company B Company C
Product 1
...

**0**

votes

**1**answer

461 views

### Excel formula to sum the total for a specific person in a specific month

I need help with an Excel formula that sums the total or work for a specific person in a specific month. Here is what my data looks like:
I need to setup a formula to extract automatically the sum ...

**0**

votes

**1**answer

287 views

### Calculation of hour in Excel using [h]:mm formula

START END TOTAL TOTAL
21:00 3:15 6:15 6:15
23:25 1:11 1:46 1:46
20:25 5:20 8:55 8:55 ...

**1**

vote

**3**answers

226 views

### formula for date to fill its self out but results stay hidden when date is blank

I have made a spreadsheet that when I put in the date at the top of the header it automatically fills in the rest of the spreadsheet. BUT, I would like for the results to stay hidden when there is not ...

**2**

votes

**1**answer

5k views

### How to hide formula results until conditions are met

I am completely new to making spreadsheets with Excel and even more dumb when making formulas. I have come to surprise myself how much I have learned, but now I am stumped and could really use some ...

**0**

votes

**1**answer

522 views

### How to do a sliding window in an excel table?

I have a table with a date column and a value column like this
|A |B
1 |date |value
2 |2013-12-10 |1
3 |2013-12-10 |3
4 |2013-12-09 |2
5 |2013-12-04 |5
6 |2013-12-02 |2
Dates can ...

**0**

votes

**0**answers

77 views

### Is there something better than VLOOKUP?

I'm wondering if there is something like a VLOOKUP for multiple matches in one column.
So, for example, if I have the following:
Alfred red
Jone orange
Christina blue
Danielle blue
...

**1**

vote

**1**answer

291 views

### Add cell data to new column at the end of each row in excel?

I have a spreadsheet and I need to combine the information in three cells and add the combined information to a new cell on the same row. For example, I have columns A through M and rows 1 through 10. ...

**1**

vote

**2**answers

102 views

### What functions can I use to track subscriptions?

I am struggling to make a spreadsheet which will help me take control of member's subscription fees. Fees are paid per month.
The spreadsheet has 12 horizontal cells, each one for a different ...

**0**

votes

**1**answer

583 views

### How to “Fill Down” more intelligently in Excel?

I have a formula in cell A1:
=VLOOKUP(E2,Master!A2:C560,3,0)
If I do "fill down" to paste this formula into all the cells below, the formula changes as follows:
=VLOOKUP(E2,Master!A2:C560,3,0)
...

**0**

votes

**2**answers

979 views

### VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data
From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg.
I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...

**1**

vote

**1**answer

54 views

### How do I lookup using 2 values, Verticle and Horizonal? (Apple Numbers)

I have a sheet that has an individuals info, one table as their Age (TABLE 1). Another Table has a score in cell (TABLE 2). I want to lookup the VALUE that corresponds with their age and score in ...

**0**

votes

**1**answer

52 views

### multiple IF Calculation excel

I currently have this formula in Excel:
=IF(G1+I1>E77,H1)
Sometimes I want it to be like this:
=IF(C1+E1>G1,D1)
I need both these formulas in the same cell but there will only ever be a ...

**0**

votes

**2**answers

68 views

### Force a one-dimensional array into a constant in Excel

I have a condition in an Excel IF-statement as follows:
INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))=1
where
INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))
eveluates to ...

**0**

votes

**1**answer

2k views

### How can I find max value and display text next to it in Excel?

I need a formula that will find the highest number in a column and then display the text in the cell immediately to the left of that number, i.e. find the highest grade and display the name of the ...

**0**

votes

**2**answers

19k views

### Comparing two columns in different Excel spreadsheets

I have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in ...

**0**

votes

**1**answer

98 views

### Replace cells in spreadsheet with cells from different spreadsheet in Excel 2010

I have a spreadsheet with phone numbers.
347-286-3341
347-286-3342
347-286-3343
347-286-3344
347-286-3345
etc...
I have another spreadsheet with phone numbers and corresponding names.
...

**0**

votes

**3**answers

95 views

### Excel Using Reference for Worksheet names [duplicate]

I've looked around to try and resolve this, however I am unfamiliar with the coding.
Here's my line of code,
='[WorkBook]BOM'!G6
What I want to do is make the Workbook call to a cell so I can mass ...

**0**

votes

**3**answers

63 views

### Excel counta function fixed reach [duplicate]

I have a planning sheet in Microsoft Excel 2010 for my company. I have made a screenshot of the planning sheet which you can see below. In the column CAP the planner can see how much capacity that ...

**0**

votes

**1**answer

34 views

### Summarizing the sales data to monthly

I'm having the following table of data:
Customer 14-Sep-12 30-Sep-12 1-oct-12 28-Nov-12 5-Nov-12
ASTDU 4718.00 3885.00 1680.00 3058.00 2147.00
BJURT 3646.00 4557.00 ...

**1**

vote

**0**answers

137 views

### automatically transfer any data on excel spreadsheet to master spreadsheet

I work in a library and I am working on a workbook to track our orders from one company. I have three sheets where each sheet represents a different branch of our library. I am trying to have it set ...

**0**

votes

**1**answer

114 views

### How to change a row of cells on excel 2010 depending on different cell data

Can anyone help me with a problem I'm having on excel 2010 please?
I am trying to change the colour of part of a row of cells when certain data is input. For example I want cells A13 to G13 to ...

**0**

votes

**1**answer

104 views

### sum values based in agent ID

I have a sheet with agent id's in column a and under each ID is a sub identifier with a value in column b of 1 - 7
sub identifiers are
Break_1000
Meeting_2000
Training_3000
Personal_4000
...

**0**

votes

**1**answer

3k views

### Check if a Row Range Contains another Row Range values in Excel

I have an Excel file that contains two different columns having Customer Numbers (let's say CustomerA and CustomerB columns). What I would like to do to check which of the CustomerA values match the ...