Posted on 2015-01-06
Medium Priority
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?

Question by:gabrielPennyback
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
LVL 26

Expert Comment

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.
LVL 26

Accepted Solution

ProfessorJimJam earned 1000 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


Author Comment

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.


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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 26

Expert Comment

ID: 40534732
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 40535451
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
LVL 49

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.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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