Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidate data using 3d function AND indirect
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Use of Indirect function to fetch data from another Excel file | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions |