asked on

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

Professor J

i recently solved a question like this. check this EE link https://www.experts-exchange.com/questions/28591608/Sumifs-with-3d-reference-to-sum-across-tabs-based-on-three-criterias.html?anchorAnswerId=40534361#a40534361

and let me know if it works for you.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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))

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.

Thanks,
John

Professor J

Sure

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

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