adding indirect values

uk747

Well-known Member
Joined
Jul 20, 2011
Messages
828
Office Version
  1. 365
Platform
  1. Windows
currently have
=INDIRECT("'"&A1&"'!"&"C10")

which shows the sheet name in A1 and the value in C10
If i wanted to add C10+C20+c32 whats the easiest way or if a range i.e. C10:C16

DO i have to use =INDIRECT("'"&A1&"'!"&"C10") +INDIRECT("'"&A1&"'!"&"C10")+=INDIRECT("'"&A1&"'!"&"C32")
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
INDIRECT("SUM('"&A1&"'!"&"C10,'"&A1&"'!"&"C20,'"&A1&"'!"&"C32)")
would work
That doesn't work for me..

For non adjescent cells, you do have to do the indirect+indirect+indirect method
=INDIRECT("'"&A1&"'!"&"C10")+INDIRECT("'"&A1&"'!"&"C20")+INDIRECT("'"&A1&"'!"&"C32")

For the Range C10:C16 it's easy..
=SUM(INDIRECT("'"&A1&"'!"&"C10:C16"))
 
Upvote 0
thanks the INDIRECT("SUM('"&A1&"'!"&"C10,'"&A1&"'!"&"C20,'"&A1&"'!"&"C32)") didnt work for me either got a ref error

although seem to have another problem now the indirect A1 is a date i.e. 1 may (formatted as d mmm) but when you look at the cell it says 1/5/2015 the tabs are in the format 1 may 2 may 12 may etc
 
Last edited:
Upvote 0
Howveer, for the non adjescent cells
If i wanted to add C10+C20+c32 whats the easiest way or if a range i.e.

You might be able to use SUMIF if there's anything common about the cells you want to add.
Like a Header row/column that contains certain text..

You could do
=SUMIF(INDIRECT("'"&A1&"'!"&"A10:A32"),"certaintext",INDIRECT("'"&A1&"'!"&"C10:C32"))

This is saying sum column C if the corresponding cell in column A = "certaintext"
 
Upvote 0
although seem to have another problem now the indirect A1 is a date i.e. 1 may (formatted as d mmm) but when you look at the cell it says 1/5/2015 the tabs are in the format 1 may 2 may 12 may etc

Use the TEXT function on A1 in your INDIRECT function..

=SUM(INDIRECT("'"&TEXT(A1,"d mmmm")&"'!"&"C10:C16"))

May is a little ambiguouse, being the only 3 letter month.
Are the tabs using the full month name, like JUNE JULY AUGUST
Or 3 letter abbreviations JUN JUL AUG
??

If they're all 3 letter abbreviations, change
TEXT(A1,"d mmmm")
to
TEXT(A1,"d mmm")
 
Upvote 0
Tabs are only using d mmm e.g. 2 may, 19 may

use simialr to above =TEXT(LEFT(F2,6),"dd mmm") to get rid of the 2015, just the days that were a problem like 02 instead of 2. so now used d mmm based on your formula, seems to be ok
 
Upvote 0
If you're going to be using this alot, you might be better off doing the conversion from real date to text in another cell.

Say B1 will have the formula
=TEXT(A1,"d mmm")

Then let your indirect formulas refer to B1 instead of A1.
 
Upvote 0
what about a range of sheets instead of a range of cells

instead of
=SUM(INDIRECT("'"&A1&"'!"&"C10:C16"))

what if i wanted the tab names in A1:G1 and adding up C10:C16 in those tabs
something like
=SUM(INDIRECT("'"&A1&"'!"&"C10:C16")):=SUM(INDIRECT("'"&G1&"'!"&"C10:C16"))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top