Solved

SUM(INDIRECT WITH MULTI-SHEET REFERENCE

Posted on 2015-01-06
7
108 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

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