Solved

SUM(INDIRECT WITH MULTI-SHEET REFERENCE

Posted on 2015-01-06
7
98 Views
Last Modified: 2015-02-05
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
Comment
Question by:gabrielPennyback
7 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
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 26

Accepted Solution

by:
ProfessorJimJam earned 250 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

=SUMPRODUCT(SUM(INDIRECT("'"&SHTS&"'!"&CELL("address",C24)))+SUM(INDIRECT("'"&SHTS&"'!"&CELL("address",G41))))
0
 
LVL 1

Author Comment

by:gabrielPennyback
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))

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
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40534732
Sure
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40535451
With your formula:

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

=SUM(INDIRECT(ADDRESS($H$41,3,1,1,C11)))

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)

Thanks
Rob H
0
 
LVL 47

Expert Comment

by:Martin Liss
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

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.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question