consolidate data using 3d function AND indirect
well Dave i tryed realy hard but no luck, cant figure that one out
but i got another solution from mr.bak i think u would find ok
1. Make a new sheet - name it First - move to leftmost and hide it
2. Make a new sheet - name it Last - move to rightmost and hide it
(all new sheet should be inserted betwin those 2 automatic )
now u can get a sum of all or som of the sheets like
SUM(First:Last!u1) - gives all sheets u1's
SUM(sheet3:Last!u1) - gives all value from sheet3 to last
SUM(First:sheet2!u1) - gives all values from first to sheet2
pm.
"Dave Breitenbach" skrev:
I'm trying to consolidate cell "u1" across multiple tabs using the SUM
function.
The following works for me:
=SUM('tab 1:tab 2'!U1) [resulting in 2 if both u1's are 1]
I know the name of the first tab but the name of the last tab I want to be
determined using the indirect function which will refer to a cell which has
the name of the last tab in it. I've tried the following:
=SUM(INDIRECT("'tab 1:"&$T$5&"'!U1"))
where t5 equals "tab 2", but I get the #REF! error.
Any help wuold be greatly appreciated.
tia,
Dave
|