# SUM(INDIRECT WITH MULTI-SHEET REFERENCE

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
LVL 1
###### Who is Participating?

Commented:
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

Commented:
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

Reliability Business Tools Analyst IIAuthor Commented:
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

Commented:
Sure
0

Finance AnalystCommented:

=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

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.