INDIRECT Function and adding cells across multiple sheets

[Jade Sheldon]

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'"),K12,"<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!

[Jade S]

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??

[MyVeryOwnSelf]

> 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.