CODEWITHSUNDEEP
excelmacrosexcel-formulaformulas
Code Newby
Code Newby

Reputation: 3

How do I reference a sheet name in my SUMPRODUCT Formula in MS Excel?

I am trying to figure out how to reference a worksheet name in my SUMPRODUCT cell and I can't figure it out. I am not sure if its because my logic is flawed and there is a better way or if what I want to do isn't possible without using VB code.

Basically I have a workbook that has various data. However for this instance I am only looking at a single column, in this case, the DATE. So I am basically using a SUMPRODUCT Formula to read the entire column for a date that is older than 120 days, but not counting items in the column that are blank. So just cells with a date in it.

This workbook will have worksheets added every month and the previous month will be stored. To make this workbook Dynamic, the first work sheet has various graphs to display information and so it can be printed nicely while the last page has all the formulas.

Basically I looking to create a drop down box on the first worksheet. That dropdown list will have the previous months (the worksheets are named by months), and ideally they will be referenced into the formula as it changes.

For example:

My current formula works: SUMPRODUCT(('OCT 2015'!$G:$G<TODAY()-120)*('OCT 2015'!$G:$G<>""'))

I would like the formula to like: SUMPRODUCT(('CELL_THAT_HAS_SHEETNAME'!$G:$G<TODAY()-120)*('CELL_THAT_HAS_SHEETNAME'!$G:$G<>""'))

And that cell it will reference is a dropdown list and corresponds to the sheetname.

Upvotes: 0

Views: 1651

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You would incorporate the INDIRECT Function:

=SUMPRODUCT((INDIRECT(A1&"!$G:$G")<TODAY()-120)*(INDIRECT(A1&"!$G:$G")<>""'))

Upvotes: 3

Related Questions