# Excel - COLUMN Function Problem - Expert Solution

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
User 23/08/2018 - 06:06
Sent - did it arrive?
Excelchat Expert 23/08/2018 - 06:06
Yeah
Excelchat Expert 23/08/2018 - 06:07
User 23/08/2018 - 06:08
You are? Or do you want me to explain?
Excelchat Expert 23/08/2018 - 06:08
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
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:
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
User 23/08/2018 - 06:34
Excelchat Expert 23/08/2018 - 06:35
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.