INDIRECT Function

[Paul Moles]

I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL.

Simple point and click calculation provides
=+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John!G10, however this allows
no flexibility to add in only part of the team. eg combined results for DICK
PETER JOHN

I had expected to be able to write
=INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10.

And then by temporarily deleting the unwanted names from the listing in A1 -
A6, provide a revised Total.
NB: As each sheet is different I appreciate the names need to be in the
correct order A1 - A6 for the cells to be correctly referenced.

Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6
nicely provides a heading for the revised report but I am struggling with the
INDIRECT function and does INDIRECT allow for some references to be blank. A2
has been temporarily deleted.

I have tried the Help file but it doesn't seem to allow for a scenario using
sheet names.

Any help appreciated.

Many Thanks

Paul Moles

 

[Pete_UK]

If you have something like:

=INDIRECT("'"&A1&"'!C2")

(the apostrophes allow for having spaces in the sheet names) and A1
contains Tom then this will return the value from Tom!C2. However, if
A1 is empty then it will return #REF. So, you will need to check for
this, like:

=IF(A1="",0,INDIRECT("'"&A1&"'!C2"))

So, you will need to build up your composite formula like this:

=IF(......) + IF(......) + IF(.....)

and so on, where each IF term is looking at a different sheet.

Hope this helps.

Pete

 

[Gary''s Student]

Just test if you little table has a blank:

=IF(A1="",0,INDIRECT("Sheet1!C1"))+IF(A2="",0,INDIRECT("Sheet2!C1"))+IF(A3="",0,INDIRECT("Sheet3!C1"))

Just change the names in the indirect to match the names in the table.

You can also adjust the cell addresses.

 

[Jacob Skaria]

Hi Paul

With data arranged in the below way in ColA in Total Sheet; try this

Col A
TOM
DICK
HARRY
PETER
BILL
JOHN

--Make sure the sheet names do not have a space after or before the entry
--Check whether the cell references for the names are in the order
C2,B20,D8,M19,F18,G10. If not correct the formula
--The formula sheet is named as 'Total' and I assume Total!Z65536 do not
have any entries.

'The contribution piece
="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6)

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<>"",A1:A6&"!"&{"C2","B20","D8","M19","F18","G10"},"Total!Z65536")),"<>0"))


If this post helps click Yes

 

[Jacob Skaria]

Since you are using this formula in Total the sheet reference to 'Total' is
not needed.

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<>"",A1:A6&"!"&
{"C2","B20","D8","M19","F18","G10"},"Z65536")),"<>0"))

If this post helps click Yes

 

[Jacob Skaria]

Had a look at it again..You can avoid referring to an odd cell.

'Again array entered...Use Ctrl+Shift+Enter to apply the formula.

=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<>"",A1:A6&"!"&
{"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<>0"))

If this post helps click Yes

 

[Paul Moles]

Don't think I made myself quite clear,
I can see how this checks if the referred cell is blank but not how it then
gets the Sheet Name (TOM DICK or HARRY etc) into the formula if it is not
blank.
I expected the function to be able to use sheet names INDIRECTLY, from the
list in cells A1, A2, A3 etc of the Total sheet to generate the completed
formula, with the cell reference from that sheet.

Effectively =indirect("TOTAL!a1"))c19 is equivalent to =+Tom!C19.
Your IF statement allows for cell TOTAL A1 to be blank but where am I going
wrong with the INDIRECT formula?

Appreciate the help
Thanks
Paul

 

[Jacob Skaria]

With the sheet names and cell references in ColA andCol B as below you can try

Col A Col B
TOM C2
DICK B20
HARRY D8
PETER M19
BILL F18
JOHN G10

(array formula: entered using Ctrl+Shift+Enter)
=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<>"","'"&A1:A6&"'!" &
B1:B6, CELL("address",A1:A6) )),"<>0"))

Thanks Pete for reminding. I thought I have put the single quote but that
was for the above version which I tried earlier (using Col B for cell
references).

The original formula modified to handle sheet names with spaces.

(array formula: entered using Ctrl+Shift+Enter)
=SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<>"","'" & A1:A6&"'!"&
{"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<>0"))


If this post helps click Yes

 

[Paul Moles]

Excellent Thank You
Does just what I wanted

Paul

 

[Pete_UK]

You're welcome, Paul - thanks for feeding back.

Pete