Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2, Sheet3, Sheet4, and Sheet5) are set up the same way, with their regional total expense amount in cell B12.
On the Summary sheet of your workbook is “B12” (without the quotes) in some cell, for this example cell E1 as seen in the following picture. Now, using the INDIRECT function to refer to that cell value in E1, and the ROW function to help refer to each of the four source sheet names, you can enter this formula as seen in cell B3…
=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)
…and copy it down as needed. This returns the value of cell B12 in each worksheet.
The trick is simply to plan ahead when you design your workbook to allow for a single formula to do the work of many formulas. And the beauty of this system is, if you change the location of the Total cell on the regional worksheets, such as if another expense item is added, simply modify cell E1 on the Summary sheet for that new Total cell address.
Hi Tom,
Thanks for sharing this info. I am very new to Excel and learning a lot. I just started a position with a Biotech company that heavily uses Excel to crunch feasibility data using Excel. Being new to Excel, I do not know all the power it harnesses as of yet!
Would you know how I can get specific cells of text from the same location on different spreadsheets into one area on a summary page. I do not know VBA, but am wondering if that is the only way to do this? I am having to cut and paste from each sheet to the one summary page, one by one. Tedious!
Thanks jenn
Hi Jennifer, thank you for following my Excel blog.
It’s hard to say exactly what solution to offer you because your question can encompass many factors. Examples are where the data is located, which sheet(s) exactly you want to search, if you are searching for a word that is specific and only part of a cell, (example, if you are searching for “tom” but maybe a cell contains the name Tom or the word tomorrow or atomic). Post back with more details because your request sounds do-able on the face of it. Here are a few more links that might put you in the right direction, though again, not knowing your workbook set-up, I can’t say for sure how relevant these links might be:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-parsing-data-to-create-and-populate-separate-workbooks/
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-summing-cells-across-worksheets/
-Tom
Hi,
The above trick is really amazing. Saved lot of time.
I have one more query
I have a excel file with 12 worksheets. My aim is to calculate the number of occurrences of 3 different words in all the worksheets
I want to know if thr is any formula to get result like below
word 1: 29
word 2: 89
word 3: 71
Waiting for ur reply.
Hi,
I have around 12 worksheet for dfferent functionlaity in one excel and each worksheet contains queries with status ‘Open’ and ‘Closed’.
Now I want total open and closed in summary worksheet in same excel in below format:
Functionality Open Closed
Login 01 15
Register 00 20
Search 05 00
Waiting for your reply. Thanks in advance.
This is the EXACT type of thing I am trying to do. I copied the format of =INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$E$1)
and it worked on a blank worksheet…HOWEVER…on the worksheet I created, I have a tab named “List” & “Data” and the other tabs are numbered ‘1’ thru ‘100’ . I only want the info pulled from the numbered tabs/sheets (1-100) onto my summary sheet.
1. It seems these named tabs are throwing off the formula and it does not work.
2. How do I change the name from ‘Sheet” as per the formula example to the numbered tabs that I have?
Please help. There has to be a way to where I don’t have to click on a cell press = and then go to the corresponding page/cell for 100 pages.
Thanks for following my Excel blog Jenn.
Yes, your question has a solution that would not force you to hit = and click on every cell of every one of the 100 sheets. However, I need to know how your workbook (I think you meant “in the workbook I created” when you wrote “on the worksheet I created”) is designed and what exactly you want to sum.
For example:
• Is it summing one single cell address on those 100 sheets?
• Or are you summing a range of cells on those 100 sheets?
• And (important) is it the SAME cell address and/or the SAME range on all 100 sheets?
• What IS the cell or range address of interest on those 100 sheets needing to be summed?
• Exactly how are those tabs named? 1? 2? 3? or are they Sheet1, Sheet2, Sheet3?
• Are all those 100 tabs arranged among themselves without any intervening sheets, meaning, your List and Data sheets are maybe the two leftmost tabs and your other 100 sheets are arranged without any non related sheets between them.
No matter how your workbook is arranged and no matter what sheets you want to include or exclude, the solution is possible. I am asking these questions to understand how your workbook is set up to see if a native formula will suffice, or do you need a User Defined Function with VBA to handle whatever craziness is happening due to the design that needs to be taken into account.
– Tom
Hello, i got the same problem as Jenn had.
If you still can help with this issue, my workbook have 44 sheets with specific names, for instance “01_TRIAGEM_CAMINHÕES” and “05_PONTO_DE_ÔNIBUS” but it doesn’t follow any logic, and there is a sheet named “RESUMO” (summary) where I want to list what’s in the cell L154 of each sheet.
The list in “RESUMO” should start at cell M15.
I also have already used a Macro for the sheet “RESUMO”, and i have tried to use other macros but it seems like I can’t make more than one macro run in one sheet (i don’t understand much of VBA)
No chances I can change the sheets’ names eather.
I want to learn how to do it automatically because there’s going to be more workbooks like this where the reference of each sheet (L154) might change.
Thanks,
Laura
Your problem is solvable but some understanding of the logic in your workbook would be needed.
• To confirm, is it that *every* worksheet except the RESUMO worksheet would need to be calculated.
• You want to list what is in cell L154 of each worksheet but then you say that cell address may change. Will the cell address change for every worksheet the same way or will it be a different cell for different worksheets.
• Why will cell L154 change — is it at the bottom of a list that will always be in column L, or will it be in the last column of any (any means any) data on each worksheet, or is there some other logic involved.
Hi, the cell does not change for each worksheet. It will surelly change in the next workbook, but the formula will be similar so I’ll just need to change the reference.
Anyway, I have already figured it out. Made a macro that works finally \o/
Thanks
Dear Tom,
I am facing the same problem. I have a worksheet and each worksheet is named uniquely. I have a ‘summary’ named worksheet where i want to see value of cell K15. Please help me out as provided formula is not working because my workbook has worksheet named uniquely.
Thanks in advance. 🙂
Here’s an easy way to accomplish that. Maybe you have already done this, but move your Summary sheet so it is the first (that is, the left-most tab) worksheet in your workbook.
Now, you say you want to sum cell K15 in all the other worksheets. That means, the formula will go into the Summary sheet, in any cell you want.
Suppose your second sheet (the one whose tab is immediately to the right of the Summary sheet) is named Budget. Maybe you have a number of worksheets after that, and the last (right-most tab) is named Employees.
This formula would sum for cell K15 on all those sheets:
=SUM(Budget:Employees!K15)
Dear Tom:
This is certainly a hot topic and I keep coming back to your blog for reference. Thanks! In this case I have a workbook with 200 sheets. The sheets are named arbitrarily using letters and spaces. I want to copy the values from G47 of each sheet to column B (B2:B202) and from G48 to column C (C2:C202). I’ve been trying to work with the formulas you’ve provided but keep getting #REF or some other errors. Any suggestions? Thanks so much.
Thanks David.
One idea is a User-Defined Function (UDF) that I would consider if it were me, but that depends on how VBA friendly you or your workplace is. You could do this with a macro too. With 200 sheets, I would list the worksheets’ 200 (or 201 because you said B2:B202 and C2:C202) CodeNames in maybe A2:A202 or D2:D202, and stick a UDF in columns B and C to refer to each tab name for cell G47. Or in another column which I would have assumed you’d have tried so why it isn’t working is a mystery, list the tab names in some other column and refer to them in the formula. Something strange is going on with the workbook or how things are arranged, so my first reply to you here is really a blind attempt to get the ball rolling towards a solution.
If it were me, I would find a place to store and hide two columns of data
Thanks Tom for the quick reply! I don’t have much experience with VBA or macros… and sorry for not being clear, it is actually 200 sheets of data plus the first sheet which is where I want the formulas collecting G47 and G48 from every other sheet.
I tried listing the sheet names in a column and referencing it in the formulas. If I pick a small set of sheets which don’t have spaces in the names, and do it with SUM G47:G48, it does actually return correct numbers. But problems pop up when I do INDIRECT for G47 or G48, regardless of the sheet names having spaces or not. Which formula would you use just to see if I’m typing this right?
Can you post the exact formula you are using. This might be a simple case where, because the formula is failing you only with sheet names that contain spaces, you are missing a pair of apostrophe characters to enclose the sheet name.
Hi,
I am trying to get my indirect formula to work. I have a workbook with over 100 worksheets, all are named different but all start the same with “SLIN”
I need to make a summary pulling information only from each tab. The information I need does not need to be added together or anything just listed. Also each item I need is in the same cell location on each worksheet. The above formula is not working.. Can you help?
Four clarifications needed:
(1)
What does “from each tab” mean. Does every sheet (every means every) start with SLIN, or are there any sheets (any means any) that do not. I assume that at least one sheet does not start with SLN but that needs to be clarified.
(2)
What does “just listed” mean. What exactly do you want to end up with. Maybe a list of over 100 cells on a summary sheet, down a column or across a row, each of those 100+ cells showing an SLIN worksheet’s respective cell value. So, go ahead and reduce the mysteries here because no one reading this has ever seen your workbook except you: What is the starting cell of this 100+ cell list on the summary sheet, is the list down a column or across a row, and what is the cell address on all these SLIN sheets.
(3)
Are all these SLIN sheets contiguously arranged together, meaning for example, that all 100+ sheets are not interrupted by a non-SLIN sheet among them. Yes or No.
(4)
Are you expecting a formula solution or a more flexible VBA solution.
Good day
I need to make a sheet that collects data from cell e57 on each sheet named MAY 1 through MAY 31,when trying the formula all values are showing 1 except MAY 21 shows 4 and from MAY 26 just show reference
I have the same problem but don’t see where the question was answered? The formula works perfectly only if I have every tab labelled “Sheet1″ Sheet2” etc. The tabs are “1” “2” “3” etc. 1 to 35.
This blog entry has gotten quite a bit of attention; it must be a common issue I stumbled into when I posted this solution. Through my replies in these comments, I answered the question generally 3 ways:
(1) Plan ahead about how you want to name your worksheets if you anticipate wanting to sum ranges on many sheets.
(2) You can utilize the worksheets’ VBA object CodeName with a UDF, without needing to worry about the tab names.
(3) You can simply do what this picture indicates, which I just did to change the sheet tab names (the way you said yours are) which automatically updated the formulas as you see in the formula bar for the selected cell and range.
Hi Tom,
Thank you for the formula. Is there any way to make it work even if the worksheets all have different names and don’t follow a pattern?
Thanks
See if this link helps:
http://www.atlaspm.com/toms-tutorials-for-excel/tom-s-tutorials-for-excel-viewing-all-worksheets-with-one-indirect-formula/
Hi Tom,
Similar to the queries above, could you help with my issue…
I want to copy out cell D17 from every one of a number of worksheets (the number varies so a flexible formula would be ideal here), which are randomly named – see below
For example I would like a final summary table to contain the following information acquired from the worksheets of the same name.
Worksheet D17
KBD01 11 AC 1.6
KBD01 11 AS 1.4
KBD01 11 BC 1.7
KBD01 11 BS 1.5
etc.
Not familiar with VB so a formula if one exists would be greatly appreciated and save me a HUGE amount of time since I have a number of spreadsheets to do this for.
Dear Tom,
I was searching for my issue, i came upon to your site. it looks wonderfull, and i can learn a lot from your thank you for your service and support to all of us
I am creating an excel workbook
each worksheet is a collection of data of one employee. so worksheet will be having the name of the employee.
in a worksheet, in column C16 to c54 there will be Variables and in column g16 to g54 the values for these variables are captured. this variable will be common for a set of employees belonging to a department. then for another set of employees belonging to another department these variables will change
for ex Dept 1 variable will be
20BD
23GH
24Ck
while Dept 2 Variable will be
45KH
49LP
53NM
…….
i need to count the value of variables against these from the various sheets all named after the employee!
these set of variables will be sharing the Same Cell location in their respective sheets
for ex for Dept 1 employee Sam , the sheet name will be SAM column C16 will have 20BD
where as for Dept 2 Employee Mark, the sheet name will be MARK column C16 will have 45KH
In the consolidated sheet, will be like this listed one below the other
C16 onwards G16 onwards
20BD Count of 20BD from all employees
23GH Count of 23GH from all employees
24CK Count of 24CK from all employees
45KH
49LP
53NM
The Variable 45KH will be in the cell C19 in the consolidated sheet while in the employees sheet it will be in C16.
I tried IF , Indirect …. but i couldnt get this done
can you please suggest me a solution
Many thanks
Hi everyone.
I’m wondering if anyone could help me.
I have around 800 workbooks with about 30 sheets each. They all have different filenames and sheet names. And they are in different folders.
The only thing they have in common is that all the data is in the same cell position.
In order to organize the data I need some kind of function/code wich can retrieve the data on command.
For example:
In each file there is a value that only appears on that file. That value is always in the same cell position. So by entering that value is it possible for the code to retrieve all the data present in the file that has that value?
Could you guys help me with this?
Bare in mind that I have no knowledge of VBA coding. So if possible could you walk me through it?
Thanks in advance for your help.
Hey. Did you solve this? I have a similar issue.
400 worksheets based on a template – and I need to collate one consistent cell value from each of the worksheets. I have a list of the locations of each worksheet – basically they all sit in their own folder within a master folder on a sharepoint site.
It’s not really an option to open each sheet – but I can “predict / construct” the path address of each cell I want to retrieve. each address would be https://team.abc.com/sites/etc
Tom?
Some of what you wrote is not clear.
Example 1:
“400 worksheets based on a template…”
Is it really 400 worksheets in 1 workbook, or is it 400 workbooks, in each is one particular worksheet with one particular cell of interest.
Example 2:
What does “collate” mean to you. It could mean compile a list, or concatenate all 400 values if they are text, or sum all 400 values if they are numbers, or maybe you mean something else altogether.
Example 3:
“It’s not really an option to open each sheet…”.
See Example 1 — worksheet or workbook?
Also, if as you say you can predict or construct a path, why can you not essentially compose a paste link type of formula to gather these cell values that way?
Hi all:
I have been looking for what should be a simple answer, but haven’t seen it yet so I am asking for it out right.
I want to have a sheet look in a specific cell location in its prior sheet that is a sum of its column, it needs to be dynamic, cannot just copy/paste because value in prior cell location can change and it needs to update the next sheet.
Thanx in advance for any help 🙂
Please clarify with examples…
– what ‘prior sheet” means.
– which column on which sheet is being summed.
– what “update the next sheet” means.
Basically, provide an explanation of your workbook, and an example of what results you expect.
HELP: I have a long spreadsheet with fixed column names. I want to sort the data in different worksheets within this workbook. My question is “How to make a change in the first worksheet that will cascade to other worksheets in this workbook even though the data are sorted in different ways?” So I want to add data in cell A2 in the “master” worksheet, but this might be sorted into cell A3245 in the second worksheet and be cell A629 in another worksheet.
Hi Tom,
Please help. I have the formula below and it works fine for the month of January. However, I would like to set it up so I can drag copy the formula across the column instead of go to single cell and change (for ex. AL:AL (for Jan) to AM:AM (for Feb), etc…)
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&TO&”‘!AL:AL”),INDIRECT(“‘”&TO&”‘!B:B”),$B53,INDIRECT(“‘”&TO&”‘!C:C”),$C53))
TO = range for (Sheet1, Sheet2, Sheet3, etc…)
AL = January column; AM = February data column; etc…
B:B = employee name column
C:C = employee task #
Also, not all the sheet has January in column AL or February in column AM (though all the months are in the same row (Row 17) for all sheets , how can I twist the formula to look for Jan month of all sheets and total them instead of stating AL:AL or AM:AM as it may grab wrong month data.
Thank you very much in advance, Tom.
Thanks for following my blog examples.
I’m not sure without seeing your workbook design, but in my blog example I referred to cell addresses by their row and column position relative to where the formula is. I see that you have hard-entered column AL which is column 38. If for example your formula is in cell D53, then you’d refer to column AL as ADDRESS(row(), column() + 34). The column refs would correspond to the relative position of the formula and therefore increment by 1 (meaning AM, AN and so on).
Hope that helps, as my first observation from a distance.
Hi Tom,
What about extracting data from same cell same worksheet in different workbooks?
Is it possible to do it?
I got 650 files. Each have a worksheet called Sheet1 lets say and I would like to extract the same cell location from each file.
I am okay with VBA tho I have no experience in that! Thanks
If the workbooks are all in the same path and folder, you can loop through all of them with VBA to extract the data you need. And the workbooks would not need to be opened at the time (but you could open them programmatically to do what you want); not that you’d want to have 650 workboosk open all at once.
This example on another blog post shows how to open and close all Excel workbooks in a specified folder. You don’t need to open the workbooks to extract the info; you *can* loop through closed workbooks with a formula that refers to each workbook name and its Sheet1 worksheet name. But in any case, see this example to get the ball rolling. The place in the code where a message box says workbooks is open and your code would go here is where the running sum would take place that the code would keep track of with each workbook, and ultimately place the final sum where you want it.
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-open-modify-and-close-all-workbooks-in-a-folder/
Hi Tom,
Could you please help on this VBA issue:
In Excel workbook, I have two sheets called “Master” and “Template”. In “Master” sheet, Column A = EmplID (cell A5:A50), Column B = EmpName (associate with each EmplID in col. A). I had the VBA code to auto-add worksheets per the EmplID list (cell A5:A50) in “Master” sheet. Running the VBA will copy “Template” sheet and generate 45 new sheets that has the sheet label = EmplID in column A/ “Master” sheet
Sub CreateAndNameWorksheets()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Sheets(“Master”).Range(“A5:A50”)
Sheets(“Template”).Copy After:=Sheets(Sheets.Count)
With c
ActiveSheet.Name = .Value
.Parent.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:= _
“‘” & .Text & “‘!A1”, TextToDisplay:=.Text
End With
Next c
Application.ScreenUpdating = True
End Sub
Now is the question: How to set the VBA to copy the EmpName that associate with EmplID to cell A1 of each new sheet
FOR EX.
Master sheet
Column A Column B
100000 Name0
100001 Name1
100002 Name2
(cell A1 in new created sheet “100000” should have “Name0”; new created sheet “100001”, cell A1 = Name1, etc…)
Your prompt help will be greatly appreciated, Tom. Thank you
Tina
Hello Tina – –
Without testing your code, just looking at it, perhaps after this line…
ActiveSheet.Name = .Value
…you could do this:
Range(“A1”).Value = .Offset(0, 1).value
Feel free to post back with a yay or nay if that helps.
Dear Tom,
I got the error message:
Run-time error “1004”:
Method “Range” of object’_Global” failed
What code line was highlighted.
your code line. Thank you. Tina
This should work. Something else is going on with your workbook. I actually told you about two code lines. One was the event code line for the Workbook level sheet change event, and the other code line being Range(“A1”).Value = .Offset(0, 1).value that would go between the lines
ActiveSheet.Name = .Value
and
.Parent.Hyperlinks…
Are you sure you placed those lines in the right places?
Hi Tom,
Below is the code with your code line in between the lines you advised… Did not work. Still show the same error message and highlight your code line.
(Thank you for taking your time with my issue. Greatly appreciated your help, Tom). Tina
Sub CreateAndNameWorksheets()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Sheets(“Master”).Range(“A5:A50”)
Sheets(“Template”).Copy After:=Sheets(Sheets.Count)
With c
ActiveSheet.Name = .Value
Range(“A1”).Value = .Offset(0, 1).value
.Parent.Hyperlinks.Add Anchor:=c, Address:=””, SubAddress:= _
“‘” & .Text & “‘!A1”, TextToDisplay:=.Text
End With
Next c
Application.ScreenUpdating = True
End Sub
This code, being a macro, belongs in a standard module. Assuming that is the case, and also there are no duplicate values in range A5:A50 of the Master sheet (because duplicate worksheet names are not allowed in the same workbook), then I cannot tell without seeing the workbook. So if there are no duplicates in B5:B50 and the macro exists in a standard module (not worksheet or workbook or class module) as it should, then something else is going on and you can send me the file at tom@atlaspm.com so I can take a look.
Hello,
I am trying to use the indirect function formula just as you have written it on this page. I am able to get this to work on a worksheet that I create from scratch with the tabs maintaining the default name (Sheet 1, Sheet 2, etc…). The problem that I am having is when I change the name of the tab to the description that I would like. I then get a #REF error. Do I need to alter the formula in order for this to work?
Thank you
The answer to your question is, yes.
My example took the simplified route of keeping the default tab names of Sheet and then a number such as Sheet1, Sheet2, and so on. That is why the INDIRECT function in this case is so short. If you have sheets whose tabs are named non-generically, such as “Budget”, “Sales”, “Variances” or what have you, those names would need to be represented in the formula somehow for the INDIRECT function to locate the proper parent object (the worksheet) and of course the cell in that worksheet holding the value you want the function to return.
Hi Tom,
I have the same problem as Richard mentioned above. If for instance the worksheets were indeed named “budget”,”sales”, “variances” (total 3 worksheets and in that order) how would you build that into the Indirect formula??….assuming i DON’T want to SUM the numbers which i retrieve from each spreadsheet. Thanks.
Hello Eddie – –
It’s not totally clear to me how much of Richard’s question applies to your question, in that his question involved sheet tab changes but you did not mention that in your question here.
So, assuming for a moment that you have 4 worksheets, where one is named Budget and another sheet is named Sales and another sheet is named Variances, and let’s further say that you want to return what is in cell A1 on each of those sheets from INDIRECT formulas located on a 4th sheet, what I did to accomplish that was:
Let’s say that 4th sheet is named Sheet1.
On Sheet1 in cell A2 I entered Budget
On Sheet1 in cell A3 I entered Sales
On Sheet1 in cell A4 I entered Variances.
On Sheet1 in cell B2 I entered this formula and copied it down to cell B4…
=INDIRECT("'"&A2&"'!A1")…and that returned, in Sheet1 cells B2, B3, and B4, what was in cell A1 for each of those 3 other worksheets.
This is a very, very old string but while I couldn’t get the initial formula in the blog entry to work, this reply did!
Thank you so very much for the knowledge you share!
Thank you!
Hello,
I have tried some of the formulas listed above to try to help me out but I still can’t get what I want to work, work. I have 84 worksheets all with different names. They are named after account codes, and so each worksheet has 5 numbers as its name. I need cell M68 from each worksheet put into a new worksheet in a list, in the order the worksheets are listed in their tabs. I know how to do this by manually typing in the worksheet name and cell number individually, however seeing as I have 84 tabs which will be increasing over the year, that is very time consuming. Please help!
hello
assuming that i have more than 2000 rows so please advise how to extract the similar 3 consuctive words in the same column
Hello,
I want that if i have 30 sheets in excel and i insert in “A1” cell 1 then how it will be shown in “A1” cell of all 30 sheets…………
I mean what formula will be used in such situation?????????
If you want a formula, you can select all the sheets where this formula should be, enter =NameOfSourceSheet!A1 and press Enter, then click on that source sheet to deselect all the other sheet tabs.
Otherwise, you can use VBA with a Change event in that source worksheet’s module to make every worksheet except that source worksheet to have their A1 cells contain the same value you enter into A1 of that source worksheet. If that is what you want — programming code — then post back and I can tell you how to do it if you do not know how.
Hi Tom,
Based on your formula =INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$E$1)
1) what is the Sheet representing? The Sheet name?
2)What if I have different Sheet names for 20 worksheets in numerical format, what do I need to change in the formula? eg. 00225,00226,00227 those are my Sheet name
Hello Jeffrey – –
The answer to your question #1 is Yes, the Sheet tab name.
For example, the formula on the Summary sheet in cell B3 is
=INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$E$1)
which refers to the worksheet whose tab name is Sheet2 because the row of said formula is 3 and 3 minus 1 equals 2. The INDIRECT function interprets that as Sheet2. Same for the other cells on the Summary sheet with formulas, subtracting 1 from their respective row numbers to match the sheet tab names of Sheet3, Sheet4, and Sheet5.
Regarding your second question, you could use the approach I show here:
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-summing-ranges-from-multiple-worksheets/
or here
https://www.atlaspm.com/toms-tutorials-for-excel/tom-s-tutorials-for-excel-viewing-all-worksheets-with-one-indirect-formula/
The concept is to have or insert a worksheet to the left and right of your worksheets being operated on.
Example:
Insert a sheet, name it Start, and place it before your first sheet of operational interest.
Insert another sheet, name it End, and place it after your first sheet of operational interest.
Then use a formula in your Summary sheet like:
=sum(‘Start:End’!A1)
Of course, modify for the cell address if it is not A1.
Keep that summary sheet tab to the far right or far left — not between the Start and End sheets.
If you insert a new sheet needing to be included in the formula, just add it between the Start and End sheets.
Thanks Tom, it works well for my question 1 except I can only use the formula start from Row 1 of my Summary Sheet (supposingly this is the main Sheet I will be using to view data reference from all other Worksheet.It is at the most left tab). When I try the same formula on Row 14 of my summary sheet, it shows #REF!
Is there anyway to solve this so that I have the flexible to start my tracking on any part of the summary sheet?
The formula would need to be adjusted to subtract or add the number (it was -1 in my example) from the row number the formula is in, to match the sheet tab suffix numbers. So if your formula on the Summary sheet is in cell C14 (14th row), and the cell you want to sum for on other sheets is in cell J5, starting on Sheet2, you’d have the formula =INDIRECT(“‘Sheet”&ROW()-13&”‘!”&J5
Maybe your situation is more complicated than that, if so, please explain.
Thanks Tom, That is helpful.
1.What if my Sheet Tab Name start from total numerical such as 00255, 00256, 00257?
2. What if my Sheet Tab Name is a total alphabetical without any number such as Jeffrey, Alice, Jimmy?
How should I modify the formula above?
Hello Tom,
Thank you so much for sharing this! This is very much helpful. But my worksheets have names according to products I am handling. Upon reading comments, you have mentioned “If you have sheets whose tabs are named non-generically, such as “Budget”, “Sales”, “Variances” or what have you, those names would need to be represented in the formula somehow for the INDIRECT function to locate the proper parent object (the worksheet) and of course the cell in that worksheet holding the value you want the function to return.”
My question is:
How can I represent these names through formula for it to be executed in the INDIRECT function? Thanks a lot!
Hello Levine – –
You can accomplish this without the INDIRECT function. Suppose you want to sum cell A1 on a bunch of worksheets, and you want that sum formula to be in some cell (doesn’t matter which cell) on Sheet1. Have that Sheet1 worksheet tab be the first (left-most) tab. Further suppose one of the worksheet whose A1 cell you want to include in the sum is named Hello and another sheet is named Goodbye. Whether there are no worksheet tabs, or 100 worksheet tabs, in between the Hello and Goodbye sheets, it doesn’t matter. All that matters is that any sheets whose numbers in cell A1 you want to include in the sum have their tabs between the Hello and Goodbye sheets.
To sum cell A1 on all sheets between and including the Hello and Goodbye sheets, on Sheet1 (or whatever the worksheet name is that you want the sum formula to be) enter this formula in any cell on Sheet1:
=SUM(Hello:Goodbye!A1)
Hello! I’m very new to excel and want to do something similar. I need 9 joining cells (H11:J13) from multiple sheets (same cells for each sheet) to appear listed after eachother on another sheet. Like the example, but the example is only one cell instead of my 9. No summing. I just want to copy the info and then keep on working with all the info on the new sheet. So 9 joined cells of info from one sheet, then preferably an empty row and then the same 9 cells from the next sheet, an empty row and so on. The sheets are named C_sub01, C_sub02 etc.
How would this formula look?
Help is soooo very much appreciated! Thank you!
Hello Emilie – –
My example used a way of having row numbers correspond to sheet names. Your question seems doable and to keep the formulas from being too cryptic by incorporating the empty row between sets of 9 cells, I’d compose a formula for each set per destination sheet. If you have a hundred sheets I might reconsider that approach, but in any case I wanted to get back to you with my initial thoughts.
I am a contributor to a very good Excel forum at https://www.mrexcel.com/forum/excel-questions/ where it is free to join, no spam, and there are experts around the clock who can offer possible solutions. Maybe give that a try and if you continue to get stuck, please post back.
Thanks for your quick answer! I actually think I solved it with a little IRL help. We did it like this: =INDIRECT(“C_sub”&Programming!A1&”!H11″), where “Programming” is a new sheet with 01, 02 etc written in cells below eachother (01 starting at A1). And so I did 9 of those formulas in 9 cells on my summary sheet, where I changed H11 according to wich of the cells between H11 and J13 that I wanted to be displayed. Then I copied it down and it works! 🙂
Thank you so much for this! This is very much helpful
Thank you, Reza!
Hello,
On the summary page in the example, if you tried to sort the Annual Expenses page it would not work. I am doing something similar and can’t figure out how to reference the same cell on multiple worksheets on a summary page AND be able to sort that summary page based on the referenced cells.
It makes sense that it would not work the way you want it to because the formula depends on a particular row to help it return the value from a properly referenced cell. That is the premise of the workbook design.
Hello Tom,
I need some help about a forumula. I have an excel sheet with more than 20 tabs. I want to run a formula on index sheet to locate if any of the tabs have any formula or calculation error. Is there any formula / VBA i can use to do this.
Thanks Tom in advance for your help
Regards
Vaib
Is there any particular error you want to locate specifically, such as #DIV/0! or #VALUE! ?
Basically it sounds like you want to loop through all the worksheets to list where formula errors are found. This will be a VBA solution, not a formula solution.
Thanks a ton Tom. Yes you are right, i wanted to loop-in all tabs to see if any of them has got any sort of error. Do you the VBA solution for this.
How exactly do you want the errors listed? If you have 20 sheets, there might be no errors on any sheet or if there are thousands of formulas on each sheet you might have a couple hundred errors in the workbook.
I do not have those many formulas in each of the tabs. I just wanted to know if any of the worksheet has any formula error. And i would like to see if i can have it highlighted on Index tab so that it will tell me to go to a particular tab and cell and rectify the error. Am i asking too much?
Thanks again Tom.
What does “highlighted on Index tab” mean? Earlier you said you want to loop through all the sheets. Are you now saying you want to loop through all except one sheet named Index? Please clarify.
I guess I confused you, sorry about that. What I really want to do is, during my review i just want to see (on the index tab) if any of the subsequent tabs in that worksheet contain any kind of error.
Any particular column on the Index tab you want to list these errors?
I have data that shows when(date & time) a certain component came into the shop and the time and date it left. My interest is to know during each day for how many hours we kept all components i.e. sum up all the hours from all the components.
First i have a sheet that shows the days each component was in the shop. So i have a row whose first column is the component’s unique number followed by the days it was on ground and the second row is the next component down to the last one. e.g.
A B C D
1 PART Day1 Day2 Day3
2 Seal 1-Jan-2017 2 Jan-2017 3-Jan-2017
3 Filter 2-Jan-2017 3-Jan-2017 4-Jan-2017
4 Ring 4-Jan-2017 5-Jan-2017 6-Jan-2017
The second sheet has the same data except that in place of the date is the number of hours the component was in the shop
A B C D
1 PART Day1 Day2 Day3
2 Seal 12 24 9
3 Filter 7 24 11
4 Ring 15 24 3
On the third sheet, i’d like to sum up the total hours from all components i.e.
1-Jan-2017 12
2-Jan-2017 31
3-Jan-2017 33
4-Jan-2017 26
5-Jan-2017 24
6-Jan-2017 3
Your help would be appreciated
Regards
Got a solution. Thanks though, your previous responses have helped me a lot
Thank you for the follow-up, Esther.
Is it possible to use the formula provided but include a VLookup function within the sum function of multiple Tabs in the workbook?
=SUM(‘601:4799’!E20)
*add in a VLookup to this formula so the SUM is going to the tab, finding the unique identifier, pulling in the value in the designated column, then returning the sum of all VLookup values found.
Is this possible?
If I interpret your question correctly, this may be a scenario to be better solved with a UDF or more likely a macro to loop through worksheets after locating a unique identifier when it might be on any sheet in any cell. In any case, VBA would be my guestimation as to how to do this, which sounds do-able on the face of it but not with a native formula.
NEW ENQUIRY
Wondering if someone can offer me assistance with this formula. I have a lot of worksheets that I need to link back to one summary worksheet. The LOOKUP formula works well, but how do I combine the INDIRECT function into the LOOKUP so that when I fill down the sheet name (unit 1) changes to unit 2, unit 3 etc? I have tried combining INDIRECT into LOOKUP but I just cannot seem to get it to work. Any help would be appreciated.
=LOOKUP(2,1/(‘Unit 1′!A:A=$B2),’Unit 1’!J:J)
I have tried using the VLOOKUP and INDEX & MATCH formulas as below but it will not pull the information from all worksheets (4 worksheets show the code #N/A). Information on the worksheets cannot be sorted alphabetically.
=VLOOKUP($B2,INDIRECT(“‘” & $A2 & “‘!” & “$A$2:$J$1048576”),10)
=INDEX(INDIRECT(“‘” & $A2 & “‘!J$2:$J1048576”),MATCH($B2,INDIRECT(“‘” & $A2 & “‘!$A2:$A1048576”)))
Hello Donna —
Just an educated guess without seeing your workbook, but if you are filling down the formula, that means your formula is populating separate rows and you can maybe tap into that. For example (untested but in theory):
Your formula
=LOOKUP(2,1/(‘Unit 1′!A:A=$B2),’Unit 1’!J:J)
can be
=LOOKUP(2,1/(INDIRECT(“‘Unit “&ROW()”1′”!A:A=$B2),INDIRECT(“‘Unit “&ROW()”1′”!J:J)
If you play around with that concept, it may get you what you want, if I understand your question correctly.
Hi Tom, Help! 🙂
Instead of having worksheets name Sheet1, Sheet2 Sheet3 etc
I have just named them as numbers ie 1 , 2 , 3 etc
Everything else for me is exactly the same! All refer to the same 1 cell on each sheet. (B35)
My Summary Sheet tab on the far left ie first one before all other ascending numbered sheets.
What small change do I need to make to the formula to make it work? Thanks so much!
Hello Elise – –
If the worksheets are named as you say with numerals 1, 2, 3 and so on, then on another worksheet akin to your Summary tab I entered this formula in cell A1 and copied it down as needed, which worked fine:
=INDIRECT(ROW()&"!$B$35")Then, in case your formulas on the Summary Sheet do not start in row 1, I entered this formula in cell J3 (the column makes no difference but the starting row number does) as an example of how to handle that situation:
=INDIRECT(ROW()-2&"!$B$35")The -2 is to compensate for row 3 being 2 rows below row 1, and 1 is the name of the first worksheet tab whose value in cell B35 you want returned for all those numbered sheet tabs as you copy down the formula from there.
Hi Tom,
I need some help getting a formula to work. I have an index sheet with Customer names which match the tab names in the file (i.e. Index cell B13=Jack & tab name = Jack etc). Since I have many files to produce with multiple customer names I need a way to pull into column “E” & “F” on the Index sheet cell C11 & C12 from each customer tab while going down the list on the index rather than manually referencing each cell on each tab manually. I have tried several of the solutions above along with a VBA but I need the index reference to each tab to remain dynamic so if changed the index will update accordingly. Any help you can provide will be appreciated
Hello Mike – –
On your index sheet, if you enter this formula in cell E13…
=INDIRECT("'"&B13&"'!C11")and this formula in cell F13…
=INDIRECT("'"&B13&"'!C12")and copy those formulas down as needed, you should get the result you say that you want.
It will not matter what order your worksheets are in.
It WILL matter if the spelling of the worksheet names in column B on the index sheet is not exactly (exactly means exactly) the same as they are on the worksheet tabs.
8/9/2020 16 4.5 0 2 6 12.5
8/10/2020 17 2.5 0 2 2.5 7
8/11/2020 18 1.5 0 1 3 5.5
8/12/2020 19 2.5 0 2 3.25 7.75
8/13/2020 20 2 0 2 2 6
8/14/2020 21 #REF! #REF! #REF! #REF! #REF!
8/15/2020 22 1.5 0 2 2 5.5
8/16/2020 23 2 0 2 3 7
Hi Tom,
As you can see in the above image showing data collected from Sheets 16 to 23, I have copied and pasted the formula =INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$I$1) for the third column in 23 Sheets and changed $I$1 for Columns 4-7 appropriately as instructed by your Tutorial. The formulas worked perfectly for all of the Sheets, except for some unknown (to me) reason, on Sheet 21, I’m getting an “Invalid Cell Reference Error.” I made sure all of sheets were labeled correctly. Why would I only get an error on just one sheet out of 23? I am perplexed…what am I missing?! Thank you for your blog!
Hmmm. Nit seeing your workbook I wonder a couple things because the issue is something that is happening on Sheet21. You said you checked that all sheets are labeled correctly but instead of just visually seeing it on the sheet tab, check again to see that the sheet is not named
” Sheet21″ (without the quotes)
or
“Sheet21 ” (without the quotes)
the idea being, maybe an invisible spacebar character is in the sheet tab name.
The other idea is to make sure that the cells in question are not merged
or
were placed differently if any rows or columns were added or deleted.
Something is going on obviously, because the other sheets are working fine for those formulas. I would check on those few ideas to see what is different in any way with Sheet21 than with the other worksheets. Post back if you are still stuck after that.
Tom,
Thank you….where/how would I “check again to see that the sheet is not named
” Sheet21″ (without the quotes)
or
“Sheet21 ” (without the quotes)
the idea being, maybe an invisible spacebar character is in the sheet tab name?”
Paul
Either double-click the sheet tab, or right-click it and select Rename. Then move your left and right arrows across the sheet tab name to see if there are invisible spaces.
Also, as I said before, you might have deleted or inserted cells on Sheet21 where where the formula is errors. Notice, I said DELETED or INSERTED. I did not say you cleared the contents of any cells. I did not say maybe you deleted or inserted any rows. Maybe you DELETED or INSERTED some cells such as selecting them and right clicking on them, and selecting Insert… or Delete… which if you did that would skew the cell references and result in the REF error you are getting.
Hi Tom,
OK that did it. There was a space after the 1 in 21.
Thanks,
Paul
Thanks for the follow-up.
Hi Tom
I am trying to find the right formula to summarise worksheets if they meet certain criteria,but I need to add new worksheets every week & want the summary to update automatically.I have been trying =SUMPRODUCT(INDIRECT(First:Last!C8:C100),B7,INDIRECT(First:Last!D8:D100)) which is returning #ref error in relation to the sheet names.
The worksheets are all formatted the same but different names ie: WE200320 or WE100820 etc.
Any help would be much appreciated.
I assume that you have all the sheets of interest between the First and Last sheets which is good.
It looks like you are trying to perform a SUMPRODUCT function on each sheet and then sum all those individual SUMPRODUCT results. If it were me, just to at least get this working the way you want, I would have a single dedicated cell (same cell address) on each of those sheets contain the SUMPRODUCT function result for that sheet. Then it is a simple matter of summing that same cell across the sheets between First and Last.
It could also be that I am misunderstanding the situation, apologies in advance if so, so feel free to post back with more information.
Thanks for the quick response Tom. Each individual sheet has days of the week with hours recorded on each day for a person & position. In the summary I am trying to achieve the summary of each day from every worksheet (between first & last) for a specific position – so B7 on the summary sheet is the position name (like “labour”) & then everyone who’s position is recorded as “labour” on the individual sheets, their hours show in the summary under each day of the week.
Does that make more sense when looking at my original formula? This was just a formula I was trying, but open to any other suggestions.
I have read this a few times and it just is not clear enough to understand.
Example 1
You wrote:
“In the summary I am trying to achieve…”
Is that translatable to:
“On the Summary worksheet, which is outside of all the Week Ending worksheets, I am trying to achieve…”
or does it mean:
“In the summary section located at cell or range [fill in what cell or range address] on each respective Week Ending worksheet…”
or does it man something else.
Example 2
You wrote:
“I am trying to achieve the summary…”
Is that translatable to:
“I am trying to achieve the SUMPRODUCT result of each respective Week Ending worksheet…”
or does it mean something else.
Example 3
You wrote:
“…their hours show in the summary under each day of the week.”
No idea what that means.
Basically, think about how no one except you has ever seen your workbook, so what seems obvious to you about abbreviating your descriptive words will mean nothing, or can mean many things, to someone who has never seen your workbook.
If you are trying to sum the calculations of each workbook in a way where the calculations (the SUMPRODUCT action in your case) is being done for each worksheet ***within the same formula*** that is summing each Week Ending worksheet’s SUMPRODUCT result, then:
(A) as I said before, I would have the SUMPRODUCT formula already in place on each Week Ending Worksheet in a single cell somewhere on each Week Ending worksheet, that being the same cell on each Week Ending worksheet, and then the formula in cell B7 of the presumably Summary worksheet (the word “summary” is being used a lot here so that is confusing me) would sum those individual cells on the Week Ending worksheet.
or
(B) I would use VBA.
Otherwise, you have lost me on what you are doing.
How can we use one formula to return the value of a different cell address that is using same formula to calculate total liability in each worksheet from multiple worksheets in a new worksheet to have those totals in one sheet?
I read your question a few times and do not understand. Try explaining again, keeping in mind that no one reading this has seen your workbook except you.
Hi Tom,
I have read through this feed and attempted formulas but cannot obtain the required result. I have a workbook of invoices with Sheets (tabs) named starting from 700 incremental to 750. I have added a sheet (Sheet1) before these tabs with the intention of summarizing information from each tab.
On Sheet1 I have a heading “Client” at cell B2 and am attempting to create a list below that heading of the client name on each tab which is always located at A10. Could you help with a formula that can be inserted into cell B3 and be copied for the rest of the column please? Many thanks Tony
You can do this with a VBA UDF (User-Defined Function), or if you do not want to use VBA you can use a less reliable but workable native formula that would tab into the worksheet tab name and the row number of the formula on Sheet1. For the latter, you should please clarify what this means:
“named starting from 700 incremental to 750”.
Does that mean you have 51 worksheets whose tabs are exactly named (exactly means exactly) 700, 701, 702, and so on to 750? or does that mean something else.
Thanks for the prompt reply Tom.
I am happy to use anything that works that will list the same cell of each of the tabs in a column on Sheet1. Yes the tabs are named exactly 700, 701, 702 etc to 750.
With the header of Client in cell B2 of Sheet1 as you said, and with the sheets of interest named as you said, then this formula on Sheet1 in cell B3 and copied down to cell B53 would do what you want.
=INDIRECT("'"&ROW()+697&"'!A10")Thank you very much Tom, that worked a treat!
Hi Tom, I tried using the formula you provided to Tony above, but I am not able to make it work. I have multiple sheets that are named 21001, 21002, 21003, and so on each rising incrementally by 1 value. I created the first sheet to be a summary sheet. A1 in the summary sheet contains the header invoice number and then each subsequent cell in the A column has the name of the related sheet (21001,21002,21003). In column B, I would like to list each customer name associated with the correct invoice. These customer names are all contained in the cell B6 in the related sheets. I am wondering if the formula you provided for Tony above isn’t working because my cell B6 is merged across B6 to E6? TIA
Merged cells are evil. But I need to understand a few things.
For starters, you need to be specific. You wrote,
“I have multiple sheets that are named 21001, 21002, 21003”
OK, so how many is “multiple”. Just say what sheet tab name those sheet tab name numbers go up to. From 21001 to what?
Then, clarify if those numbered tabs are set in the order from left to right in your workbook. If they are not, or they might not be at ANY time, then using each worksheet’s CodeName would be needed and that will involve VBA either as a macro or as a UDF. The trade-off is, a formula is quicker but more risky if the sheet order ever (ever means ever) changes or its tab name changes. Using the sheets’ codename is much more reliable but some workplaces do not allow VBA.
Then, clarify where (meaning what sheet name and in what cell on that sheet) does the list of sheet numbers exist. Presumably it is on the Summary sheet but you need to say the sheet name is Summary or whatever it is. And the cell address of that first sheet tab name number.
Hello, having a similar issue as others. I have a workbook with about 100 sheets, all of which are titled with people’s names rather than something like “Sheet 1” etc. All of the sheets are formatted the same, with the exception of 2 summary sheets. Is there an easy way to find and return the maximum value of a given cell (say E3, for example) across all of the non-summary worksheets?
Thanks!
The short answer to your question is yes.
The longer answer is, if it were me, I would use a macro or UDF (User-Defined Function) which means VBA but also means you will need to do no preparation work at all, nor would you need to update that prep work when a worksheet tab gets changed.
If VBA is OK for you, then say what the two Summary sheets are named, or better yet what their VBA CodeNames are which you can easily see in the VBE Project window. Also, make sure that cell E3 really is cell E3 or are there any merged cells involving cell E3.
Given that, a macro can be posted for you if VBA is an acceptable option.
Dear atlaspm.com webmaster, Keep it up!