Solved

SUM(INDIRECT WITH MULTI-SHEET REFERENCE

Posted on 2015-01-06
7
91 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 25

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 25

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40534732
Sure
0
 
LVL 32

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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

823 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