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?

gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?
ProfessorJimJamConnect With a Mentor 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

i recently solved a question like this.  check this EE link

and let me know if it works for you.
gabrielPennybackReliability 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.


Open in new window

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.

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Rob HensonConnect With a Mentor Finance AnalystCommented:
With your formula:


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:

B11  =SUM(INDIRECT(ADDRESS($H$41,3,1,1,C11)))   C11  Allen
B12  =SUM(INDIRECT(ADDRESS($H$41,3,1,1,C12)))   C12  Fred
B13  =SUM(INDIRECT(ADDRESS($H$41,3,1,1,C13)))   C13  Wesley

B14  =SUM(B11:B14)

Rob H
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

All Courses

From novice to tech pro — start learning today.