Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula
Here’s how you can use Data Validation with the INDIRECT function in a single formula to quickly and easily view the contents of other worksheets in your workbook.
Suppose your workbook is the company budget, designed so the company’s regions occupy their own budget worksheets. For example, the following picture shows worksheets for North, South, East, and West regions. A Summary worksheet is also designed the same, and is the focus of this example.
Step 1
On the Summary worksheet in cell H2, Data Validation is applied, with the list of allowable entries limited to the regions’ worksheet tab names. I selected cell H2 and pressed Alt+D+L to show the Data Validation dialog box. In the Source field for List, I typed in those tab names separated by a comma, as shown in the following picture.
Step 2
Still on the Summary worksheet, I selected the same range of cells that contain budget numbers for the other regions. It’s a valuable benefit from designing the workbook with all worksheets laid out the same. In the active cell (B4 in this example) I typed the formula…
=IF(LEN($H$2)=0,0,INDIRECT("'"&$H$2&"'!"&ADDRESS(ROW(),COLUMN())))
…and I pressed Ctrl+Enter which applied that formula to all selected cells.
Step 3
Now while never leaving the Summary worksheet, it’s a simple matter of selecting the worksheet name from the drop down list in cell H2 depending on which region I want to quickly see. In the following picture, I selected the East Region.
Here are the East Region’s numbers.
Can we use this formula if we have 120 bank accounts (sheets)..?
Hi chander,
I don’t know why this shouldn’t work the same way with 120 bank accounts. It surely is a little bit more work than “just” four regions but should be possible (with a little 😉 more work).
Hope that helps
Phil
Hi Tom, I have a workbook wherein a consecutive range of sheets are named differently (in particular each sheet is a person’s name.) Now, from time to time a sheet may be added or deleted within the range of sheets. I need a formula that lists the content of the same single cell on each sheet in a column on another sheet. Can you please help?
Try inserting two dummy sheets. Name them start and end. Put all the sheets you’re interested in between the start and end sheets. Sum of interest (for example, cell A1) would be this formula:
=sum(start:end!A1)
Hi Tom, I have a workbook where each sheet is named differently and I want to pull the data from the same cell on each page. Currently my summary sheet is listed in this way (see below), (where “1001” is the name of the worksheet, and so forth). I am wanting to set up my summary page so that each time a new worksheet is added, the summary page adds a new row and pulls the info. So if I open up sheet “1250” and put data into D5, then when I go back to the summary page, it shows a new row with the date from that worksheet.
‘1001’!D5
‘1002’!D5
‘1003’!D5
Thanks for any help if you still read this.
You’re welcome in advance – – I read all comments!
Questions:
(1)
Is the name of the summary worksheet really “Summary”?
(2)
Are there other worksheet besides the summary sheet whose cell D5 would not be included in the calculation, and if so, what are those non-inclusive sheets’ names?
(3)
When you say “the summary page adds a new row”, that to me means the solution should be VBA, not strictly a formula, so is that OK. If it is not OK to use VBA, then I would not suggest a strict formula solution because it would not be robust enough to be effective.
Is there anyway to do this if the tab names keep changing? The individual tab names change using a vba that makes the tab name change to whatever i typed in to cell A1 of that sheet.
There are 2 other ways to do this. One is by using the INDEX function but that can also bring you problems depending on the order in which the sheets are arranged relative to each other. I almost never use the INDEX property to reference a worksheet.
The most reliable way to do this is with a macro or user-defined function to refer to the worksheets’ CodeName property, which never changes no matter what the sheet tab name is, or who is using the workbook, or which position the worksheet is in relative to other worksheets. This would require VBA so if that is not an option for you then you are destined to have problems referring to worksheets when their names and positions change. If you know that a VBA solution is OK, then post back saying so and we can take it from there.
I am looking for help to a similar problem.
I would like to return the information in the same cell in multiple worksheets. Each worksheet is numbered 1, 2, 3, etc. and in the summary tab I am looking to populate the table rows are labeled 1, 2, 3, etc. What formula can I used that references the numbers in the table as the look up for the sheet I want the numbers populated from?
Eg.
Week Data
1 (from cell K13 on sheet 1)
2 (from cell K13 on sheet 2)
3 (from cell K13 on sheet 3)
4 (from cell K13 on sheet 4)
etc
I am looking for help to a similar problem.
I would like to return the information in the same cell in multiple worksheets. Each worksheet is numbered 1, 2, 3, etc. and in the summary tab I am looking to populate the table rows are labeled 1, 2, 3, etc. What formula can I used that references the numbers in the table as the look up for the sheet I want the numbers populated from?
Eg.
Week Data
1 (from cell K13 on sheet 1)
2 (from cell K13 on sheet 2)
3 (from cell K13 on sheet 3)
4 (from cell K13 on sheet 4)
etc
Thank you for helping to look into this.