Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default INDIRECT Function and adding cells across multiple sheets

I have been searching for an answer to this problem for a couple days now, so hopefully someone can give me a little guidance.

I have a workbook with multiple sheets and I want to add only numbers that are less than zero from a particular cell (K12) across sheets. For example, if Sheet1 K12 is -3, Sheet2 K12 is 4, and Sheet3 K12 is -5, I want a cell (N12) in Sheet4 to show a value of -8. Right now there are only those sheets but each week a new sheet will be created, so eventually there will be a LOT of sheets, too many to keep track of positive and negative numbers. Essentially, I'm looking for a running total of negative numbers in this particular cell across sheets.

I have one sheet called "Sheets" in which A1:A3 are Sheet1, Sheet2, and Sheet3 and have made that a named range (Sheets). This range will be updated every time a new sheet is created.

The formula I have so far is:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!K12'"),K 12,"<0"))

The error I receive is

#REF! "Function INDIRECT parameter 1 value is "Sheet1'!K12". It is not a valid cell/range reference."

I am thinking it may have to do with the fact that the data in the K12 cells come from the formula =IF(ISBLANK(J11),"",SUM(J11-G11)) but I can't get my N12 formula to work with just straight numbers either.

PLEASE help with this because it's driving me crazy! Thank you!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default INDIRECT Function and adding cells across multiple sheets

It might also help to add that I am using Google Docs instead of Excel, which, after more research, I found out does not support 3D referencing. I think that has to be the issue. Any ideas for a non-3D workaround??
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 56
Default INDIRECT Function and adding cells across multiple sheets

I have a workbook with multiple sheets and I want to add only numbers
that are less than zero from a particular cell (K12) across sheets.


This approach is not elegant, but it seems to get the right result in Excel 2010.

The column Sheets!B is used as a workspace.

In B1, put
=IF(A1="",0,MIN(0,INDIRECT("'"&A1&"'"&"!K12",TRUE) ))
and copy down as far as the list can ultimatele reach.

Where the answer is to go, put
=SUM(Sheets!B:B)

If a "running total" is required to showing all weekly totals, put this in a Sheets!C1
=SUM(Sheets!B$1:B1)
and copy down. (Another workspace).

If you need each Sheet to contain the total of all the previous sheets, build on Column C by using this:
=INDEX(Sheets!C:C,MATCH(MID(CELL("filename"),FIND( "]",CELL("filename"))+1,255),Sheets!A:A,0)-1)
in any sheet except the first. The MID(...) part privides the tab name of the sheet in which it occurs.

Hope this helps getting started.
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
Indirect - Multiple Sheets/Cells Keep It Simple Stupid Excel Worksheet Functions 5 November 8th 07 05:39 PM
Adding a single function onto multiple cells that already have Num J@Y Excel Discussion (Misc queries) 5 January 23rd 07 09:07 PM
Adding Cells from Multiple Sheets Kevin G[_3_] Excel Programming 3 June 1st 05 06:55 PM
Sum Indirect function through multiple sheets Andre Croteau Excel Discussion (Misc queries) 2 May 6th 05 10:44 AM
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. Steve Excel Worksheet Functions 6 November 24th 04 01:10 AM


All times are GMT +1. The time now is 02:16 AM.

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

About Us

"It's about Microsoft Excel"