Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 28th 06, 04:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 30
Default 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   Report Post  
Old September 28th 06, 07:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) sstackhoNOSPAMNO@yahooNOSPAMNO.com Excel Worksheet Functions 0 September 5th 06 03:44 PM
Use of Indirect function to fetch data from another Excel file Boon8888 Excel Worksheet Functions 1 January 20th 06 07:46 PM
Using the Indirect function with a sheet number instead of a sheet name JDB Excel Worksheet Functions 5 December 31st 05 03:03 PM
Using INDIRECT function to specify source data donesquire Charts and Charting in Excel 2 May 27th 05 03:53 AM
referencing named formula using INDIRECT function rob.hick@nbs.nhs.uk Excel Worksheet Functions 19 May 11th 05 09:48 AM


All times are GMT +1. The time now is 04:48 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017