Calculating a YTD value from multiple sheets.

[Pittviper]

Hi,

I am somewhat new to excel, but know the rudimentary basics.

What I am trying to do is simple, but just can't seem to put it all together.

I have 12 sheets, January - December, with financial info, obviously. All I want to do is, at this point, sum one cell from the identical sheets, differing only by the month name, but only up to, and including, the current month, which in this case, is February.

I know I can use the following:

=SUM(January:December!E54) to get a value, but need to stop at February. So, I also have used indirect, somewhat like the example below, which counts.

SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetnames!A1:A12&"'!E54))

and that works, where Sheetnames is a sheeet with January - December in cells A1-A12. But, I only want to use, say, in this case at this time, January and February.

I figured out how to find the current month, using:

=TEXT(NOW(),"mmmm")

But no matter what I've tried, I can't seem to combine using the above two examples, into a formula that will basically say: sum(indirect("'"&January:February&"'!E54)

I somehow need to incorporate the current month into the range of sheets.

I apologize for seeming dense on this, but I am very new to using Excel in this more advanced fashion, and am literally driving myself crazy!

So, if anyone can help, please feel free...

Thank all of you so much.

 

[Scott T]

I do not think INDIRECT can take 3D references. You can have a sheet called end and use that as the last sheet

=SUM(Jan:end!A7)

You would then move the other sheets in as need.

 

[Osvaldo Palmeiro]

... where Sheetnames is a sheeet with January - December in cells A1-A12. But, I only want to use, say, in this case at this time, January and February.

For that case this formula seems to work.

=SUMPRODUCT(SUM(INDIRECT("'"&A1:INDIRECT(ADDRESS(MATCH(TEXT(TODAY(),"mmmm"),A1:A12,0),1))&"'!E54")))

 

[Pittviper]

Thanks for the reply. In form, it looks like that should work, but does not. Get result of #N/A.

Don't I need to insert the name of the sheet that contains the month names, which is "Sheetnames", somewhere in that formula? I am a little foggy on the correct use of indirect, even though I have researched the syntax. It seems I am not quite understanding how to reference rows and columns.

Looking forward to all replies.

TIA

 

[Pittviper]

The A1 and A1:A12 used in the formula are referencing the cells on the summary sheet I am trying to calculate on, not the sheet that contains the month names.

 

[Pittviper]

As a test, I tried the following: =SUMPRODUCT(INDIRECT("'"&$I$4&"'!e54")), where $I$4 are referencing a cell on the summary sheet with the text "January" in the cell.
That will pull the figure from the January sheet.

But, when I tried: $i$4:$I$5, where cell I5 contains a formula that returns the result of "February", it does NOT work. I have a fundamental lack of understanding on how to reference cells and sheets, it would seem.

 

[Osvaldo Palmeiro]

Don't I need to insert the name of the sheet that contains the month names, which is "Sheetnames", somewhere in that formula?

You are right. Sorry for that.
I've added the sheet name.

=SUMPRODUCT(SUM(INDIRECT("'"&Sheetnames!A1:INDIRECT("Sheetnames!"&ADDRESS(MATCH(TEXT(TODAY(),"mmmm"),Sheetnames!A1:A12,0),1))&"'!E54")))

 

[Pittviper]

Hi Osvaldo, thanks again for the reply. I copy and pasted the exact formula you typed. I still get a result of #N/A. I don't even know what that means... Is it possibly an issue with nesting the indirect functions? What you have written looks logical and should work. I am totally clueless as to why it doesn't, and how to even begin troubleshooting it...

I do thank you for your assistance.
TIA...

 

[PittViper1965]

Osvaldo,

I've lost your email address, due to the fact that my hd got corrupted, and I lost all email contacts. I even forgot I was on this board, and joined again under my real email address. This is Jason Tsangaris, btw.

Can you please send me and email so I have your email address again?
Thanks,
Jason