INDIRECT Function
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
---------------
Jacob Skaria
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Jacob Skaria" wrote:
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
"Jacob Skaria" wrote:
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
"Paul Moles" wrote:
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
|