Extracting data in the same cells across multiple sheets to a summary sheet
I have multiple tabs in a spreadsheet (each one signifying a date). I want to extract the same cells in each tab onto a summary sheet. Is there an easy way to do this without any VB?
I would like the summary to look something like this:
Cell A2 (This is the date) Cell R3 Cell D4 Cell D5 Cell D6
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:
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us?
- Ray Kroc
?Actually, I *am* a rocket scientist.?
- JB (little ones count!)
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:
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.
I don't think my Sheets are formatted properly to use the indirect function. I tried using your formula and got #ref errors.
Originally Posted by JBeaucaire
Last edited by acedie; 01-05-2009 at 01:07 PM.
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
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
: How to use code tags, mark a thread solved, and keep yourself out of trouble
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.
Works like a charm, now if there were a way to grab all the sheets names and put it in a list, I will be done.
Originally Posted by JBeaucaire
Thanks a lot for what you have done so far!
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1