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