Question description:
This user has given permission to use the problem statement for this
blog.
The formula is: =SUMIF(BAARCH!A:R,summary!A6,BAARCH!P:P) which searches for the name inputted on the summary sheet on the BAARCH worksheet - but I want it to look for the name across a range of worksheets. The column is PP.
Solved by Z. C. in 50 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/08/2018 - 06:03
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert
23/08/2018 - 06:04
Do you have sample data that we can use to illustrate how to solve this problem?
User
23/08/2018 - 06:05
Yes - shall I send it?
Excelchat Expert
23/08/2018 - 06:05
Yeah, please..
User
23/08/2018 - 06:06
Sent - did it arrive?
[Uploaded an Excel file]
Excelchat Expert
23/08/2018 - 06:06
Yeah
Excelchat Expert
23/08/2018 - 06:07
Going through the data please...
User
23/08/2018 - 06:08
You are? Or do you want me to explain?
Excelchat Expert
23/08/2018 - 06:08
Elaborate a little bit please...
User
23/08/2018 - 06:10
On the summary sheet I want to look up the name e.g. Ollie Chapman across multiple worksheets e.g. BAARCH and BALAND and look up how many hours worked in column P - Ollie could be mentioned multiple times on one sheet.
User
23/08/2018 - 06:11
My example just shows a couple of works but there maybe 100 and the same person repeated.
User
23/08/2018 - 06:11
*workers
Excelchat Expert
23/08/2018 - 06:11
Okay.
User
23/08/2018 - 06:11
My formula only works for one sheet!!
Excelchat Expert
23/08/2018 - 06:14
Okay.
Excelchat Expert
23/08/2018 - 06:14
How many such like sheets do you have?
User
23/08/2018 - 06:14
I have shown two but there maybe at least 15
Excelchat Expert
23/08/2018 - 06:20
There are a couple of approaches.
Excelchat Expert
23/08/2018 - 06:21
We use VBA or have a named range of all the sheets in a workbook.
User
23/08/2018 - 06:22
Which is easier for me? What you have seen is my level of expertise - fairly low!
Excelchat Expert
23/08/2018 - 06:23
VBA is complex but easier.
Excelchat Expert
23/08/2018 - 06:23
Let's work with the latter.
User
23/08/2018 - 06:23
Thanks!
Excelchat Expert
23/08/2018 - 06:23
Processing the formula and steps for you.
Excelchat Expert
23/08/2018 - 06:31
Thank you for your patience.
User
23/08/2018 - 06:31
No problem -
Excelchat Expert
23/08/2018 - 06:32
The formula will be:
Excelchat Expert
23/08/2018 - 06:32
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A:R"),A3,INDIRECT("'"&Sheets&"'!"&"P:P")))
Excelchat Expert
23/08/2018 - 06:32
Solution:
[Uploaded an Excel file]
Excelchat Expert
23/08/2018 - 06:33
Are we together?
User
23/08/2018 - 06:33
Yes, that is amazing - you are a genius!
User
23/08/2018 - 06:34
Many thanks
Excelchat Expert
23/08/2018 - 06:34
Thank you.
Excelchat Expert
23/08/2018 - 06:34
But let me explain please.
User
23/08/2018 - 06:34
Yes please
Excelchat Expert
23/08/2018 - 06:35
That will help you modify it with ease to meet your needs.
Excelchat Expert
23/08/2018 - 06:35
Look at the summary sheet.
User
23/08/2018 - 06:35
Yes, I can see it
Excelchat Expert
23/08/2018 - 06:36
In column O, from O3, I listed the names of the sheets.
Excelchat Expert
23/08/2018 - 06:36
That is where the secret is.
User
23/08/2018 - 06:36
So I need to list every sheet when I create them
Excelchat Expert
23/08/2018 - 06:36
EXACTLY!
User
23/08/2018 - 06:37
Then it will pick up them all - clever
Excelchat Expert
23/08/2018 - 06:37
You will add it below the list.
Excelchat Expert
23/08/2018 - 06:37
After adding it, that is not enough!
Excelchat Expert
23/08/2018 - 06:37
There is something called 'A named range'
Excelchat Expert
23/08/2018 - 06:38
I named the range which contains the names of the sheet as "Sheets"
Excelchat Expert
23/08/2018 - 06:38
In our formula:
Excelchat Expert
23/08/2018 - 06:38
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A:R"),A3,INDIRECT("'"&Sheets&"'!"&"P:P")))
User
23/08/2018 - 06:38
I see
Excelchat Expert
23/08/2018 - 06:38
Where the name "Sheets" Appears, it refers to the range.
Excelchat Expert
23/08/2018 - 06:38
So, you will need to edit this range.
Excelchat Expert
23/08/2018 - 06:39
So you need to change this named range after adding a new sheet to the list.
User
23/08/2018 - 06:40
But do not need to update the formula
Excelchat Expert
23/08/2018 - 06:40
To change the range, follow with me this procedure.
Excelchat Expert
23/08/2018 - 06:40
Yeah, you do not need to update the formula.
Excelchat Expert
23/08/2018 - 06:40
You just need to change range as follows:
Excelchat Expert
23/08/2018 - 06:41
Follow with me please:
Excelchat Expert
23/08/2018 - 06:41
STEP 1
User
23/08/2018 - 06:41
ok
Excelchat Expert
23/08/2018 - 06:41
On the Ribbon, click the Formulas tab
User
23/08/2018 - 06:41
yes
Excelchat Expert
23/08/2018 - 06:41
STEP 2
Excelchat Expert
23/08/2018 - 06:41
Click Name Manager5`
Excelchat Expert
23/08/2018 - 06:42
* Click Name Manager
Excelchat Expert
23/08/2018 - 06:42
STEP 3
Excelchat Expert
23/08/2018 - 06:42
In the list, click on the name that you want to change
Excelchat Expert
23/08/2018 - 06:42
In our case, the "Sheets"
Excelchat Expert
23/08/2018 - 06:43
Are you there?
User
23/08/2018 - 06:43
Yes - just looking at the spreadsheet
Excelchat Expert
23/08/2018 - 06:43
Do you have the Edit name dialog box?
Excelchat Expert
23/08/2018 - 06:44
Have you reached at STEP 3 ?
User
23/08/2018 - 06:44
No - I have clicked on sheets and it hightlights the range you made. I am on the Mac version of Excel.
Excelchat Expert
23/08/2018 - 06:46
Okay.
Excelchat Expert
23/08/2018 - 06:46
Can you locate the formulas tab?
User
23/08/2018 - 06:47
Where you edit the formula?
Excelchat Expert
23/08/2018 - 06:48
Try this:
Excelchat Expert
23/08/2018 - 06:48
Insert > Name > Define
Excelchat Expert
23/08/2018 - 06:48
Insert > Name >
Excelchat Expert
23/08/2018 - 06:48
Can you locate that?
User
23/08/2018 - 06:48
just looking
User
23/08/2018 - 06:49
I have!
Excelchat Expert
23/08/2018 - 06:49
Yaaaay!
Excelchat Expert
23/08/2018 - 06:49
Choose Name from the Insert menu and then choose Define. Excel displays the Define Name dialog box.
User
23/08/2018 - 06:50
Yes - called sheets and has a range defined
Excelchat Expert
23/08/2018 - 06:50
In the list of names shown in the dialog box, click once on the name whose reference you want to change.
Excelchat Expert
23/08/2018 - 06:50
Very good!
Excelchat Expert
23/08/2018 - 06:50
The range is:
Excelchat Expert
23/08/2018 - 06:50
=summary!$O$3:$O$4
Excelchat Expert
23/08/2018 - 06:50
Right?
User
23/08/2018 - 06:51
Yes!!
Excelchat Expert
23/08/2018 - 06:51
Very good!
Excelchat Expert
23/08/2018 - 06:51
Now once you add another sheet to the list, for example at cell O5
Excelchat Expert
23/08/2018 - 06:51
So that our formula can factor in the added sheet, we change this named range to:
Excelchat Expert
23/08/2018 - 06:52
=summary!$O$3:$O$5
User
23/08/2018 - 06:52
I edit the range in that box?
User
23/08/2018 - 06:52
Yes! Got it.
Excelchat Expert
23/08/2018 - 06:52
Yeah, you edit it.
Excelchat Expert
23/08/2018 - 06:52
Now it is clear right?
User
23/08/2018 - 06:52
Yes - very clear
Excelchat Expert
23/08/2018 - 06:52
You are a Pro!
Excelchat Expert
23/08/2018 - 06:52
That is it!
User
23/08/2018 - 06:52
Ha ha - not sure about that. I have been trying to do this for three days!
Excelchat Expert
23/08/2018 - 06:53
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
Excelchat Expert
23/08/2018 - 06:53
You can always end and rate the chat at any time for better services. Hope to see you again!
User
23/08/2018 - 06:53
I will - thanks for this and have a good day!
Excelchat Expert
23/08/2018 - 06:53
You too!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
