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

learn more… | top users | synonyms (5)

1
vote
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
3answers
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
3answers
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
3answers
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
3answers
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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 ...