Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SUM(INDIRECT WITH MULTI-SHEET REFERENCE

Posted on 2015-01-06
Medium Priority
140 Views
I have this formula in G43: =SUM(Allen:Wesley!\$C24).   I want to use a draggable INDIRECT formula referencing G41's value of 24. H43 value = 25, etc.

This of course does not work: =SUM(INDIRECT("Allen:Wesley!C"&H41)). Is there a way to do it using INDIRECT?

Thanks,
John
0
Question by:gabrielPennyback
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 27

Expert Comment

ID: 40534656
i recently solved a question like this.  check this EE link http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28591608.html#a40534361

and let me know if it works for you.
0

LVL 27

Accepted Solution

ProfessorJimJam earned 1000 total points
ID: 40534678
i am not sure if i understood your question but if you are to sum the values of cells H and C together in all of the sheets then

put all of the sheet names in a range, then name that range as "SHTS" then use this formula

0

LVL 1

Author Comment

ID: 40534698
Thanks, ProfessorJimJam, I was able to apply it to my situation. I already had a hard-wired version of that formula and your answer steered me in the right direction.

``````=SUM(INDIRECT(\$B\$44&G\$41)+INDIRECT(\$B\$45&G\$41)+INDIRECT(\$B\$46&G\$41)+INDIRECT(\$B\$47&G\$41)+INDIRECT(\$B\$48&G\$41)+INDIRECT(\$B\$49&G\$41)+INDIRECT(\$B\$50&G\$41)+INDIRECT(\$B\$51&G\$41))
``````

But if you don't mind, I'd like to wait a while and see if someone can come up with a "Allen:Wesley!" INDIRECT. I'll definitely give you points if someone does.

Thanks,
John
0

LVL 27

Expert Comment

ID: 40534732
Sure
0

LVL 33

Assisted Solution

Rob Henson earned 1000 total points
ID: 40535451

=SUM(INDIRECT("Allen:Wesley!C"&H41))

if you highlight the section INDIRECT("Allen:Wesley!C"&H41) and press F9 it gives a result of #Ref! hence why the SUM isn't then working.

I believe INDIRECT doesn't like 3 dimensional references.

Another option would be to include the ADDRESS function:

The above would for example be in B11 and in C11 would be a single sheet name eg Allen. You would then have to repeat for each sheet and then SUM the results eg:

B14  =SUM(B11:B14)

Thanks
Rob H
0

LVL 49

Expert Comment

ID: 40590666
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll