Extracting data in the same cells across multiple sheets to a summary sheet

Extract how? Just add up all the R3s across multiple tabs? This is called a 3D range.

1. Click on the target cell
2. Type in =SUM(
3. Click on the first tab in the range
4. Shift-Click on the LAST tab in the range, they will all be highlighted now
5. Now click on the source cell, R3
6. Press ENTER.
7. Your formula should look something like this:

=SUM('Sheet1:Sheet3'!R3)

Ah, you're talking about creating an indirect reference? You can create a formula that builds up pieces of the cell reference using the INDIRECT function.

Careful, though. Not all sheetnames make good INDIRECT references, dates are occasionally problematic. What is the format of your dated sheetnames?

Anyway, if an acceptable dated sheetname is listed in A2, the reference in B2 to bring over cell R3 from that sheet would be:

=INDIRECT(A2&"!R3")

NOTE: the date you entered in cell A2 must be formatted as text for this to work. Post up a sample book if you have problems.

Sorry, I should have explained a little better. I do not need a sum of the cells across the sheets. I need to display the data on a summary tab (1 line per date).

So it would look like this:

Day1.Cell A2 (Day 1), Day1.Cell R3, Day1.Cell D4, Day1.Cell D5, Day1.Cell D6
Day2.Cell A2 (Day 2), Day2.Cell R3, Day2.Cell D4, Day2.Cell D5, Day2.Cell D6
so on and so forth for about 200 days (1 working year)

I have attached a sample of the sheets I am working with, and the desired result on the "Summary" tab.

Originally Posted by JBeaucaire

Ah, you're talking about creating an indirect reference? You can create a formula that builds up pieces of the cell reference using the INDIRECT function.

Careful, though. Not all sheetnames make good INDIRECT references, dates are occasionally problematic. What is the format of your dated sheetnames?

Anyway, if an acceptable dated sheetname is listed in A2, the reference in B2 to bring over cell R3 from that sheet would be:

=INDIRECT(A2&"!R3")

NOTE: the date you entered in cell A2 must be formatted as text for this to work. Post up a sample book if you have problems.

I don't think my Sheets are formatted properly to use the indirect function. I tried using your formula and got #ref errors.

You explain it as if each tab/sheet is for a different date so I'm not sure what we are taking from each sheet (unless each sheet only has 1 line). I don't see an attached copy of your workbook (use the "Manage Attachments"). Thanks

Here you go. As I said, your lookup cell with the dates in it must be formatted as text and EXACTLY duplicate the appearance of your sheetnames.

Highlight the first three dates in A2:A4, then grab the little black handle in the lower right corner and pull it down to get the rest of the Jan dates. Then put in a couple for Feb and pull that down. Excel can extend numerical lists like that pretty easily.