Modify code to look at previous month

[RodneyW]

I'm using this code:

=SUMPRODUCT(--('D:\Corporate\Analytics\2020Analytics\[Members2020.xlsx]Apr'!$A$3:$A$200=$B$1),'D:\Corporate\Analytics\2020Analytics\[Members2020.xlsx]Apr'!$D$3:$D$200)

which pulls information based on the tab named APR. There is a similar tab for every month. The APR tab is not populated until May, May isn't populated until June and so on. I need to modify the code above so that instead of looking at a specific predetermined tab, it looks at what the current calendar month is it will pull data from the previous month. To give it an example, right now it is September. The formula should pull from the AUG tab.

How do I do this?

Thank you in advance

 

[Fluff]

That is possible, but will need to use the INDIRECT function which is slow, volatile & doesn't work on closed workbooks.
Will that be ok?

 

[Shiseiji]

Fluff, I was thinking simple ranges and math. Maybe I missed something . . .

Rather than depending on checking the calendar since you appear to be populating worksheets, I'd use a target "range" based on date; i.e. a month. The target range can be manually created, or created using Visual Basic. I think either way I'd create all the worksheets and give them month names. You can hide the ones not in use if needed. If you want to create the ranges by hand, i.e on the August worksheet one range could be $A$3:$A$500 , I'd give them names easy to manage like "r_AUG_ & "whatever the column is" Because your ranges have simple names the formula is easily changed to "r_SEP_ & "whatever the column is" etc. The tallys can be in row A for a quick sanity check and then all together on a summary worksheet.

How comfortable are you with named ranges or Visual Basic and how automatic do you need it to be?

Hope I didn't miss read what you need.

Ron

 

[RodneyW]

FLUFF --- it might work, I'm willing to explore it.
SHISEIJI --- I'm not familiar at all with Visual Basic but am somewhat familiar and comfortable with ranges.

 

[Fluff]

How about

=SUMPRODUCT(--(INDIRECT("'D:\Corporate\Analytics\2020Analytics\[Members2020.xlsx]"&TEXT(EDATE(TODAY(),-1),"mmm")&"'!$A$3:$A$200")=$B$1),INDIRECT("'D:\Corporate\Analytics\2020Analytics\[Members2020.xlsx]"&TEXT(EDATE(TODAY(),-1),"mmm")&"'!$D$3:$D$200"))

 

[Shiseiji]

Looks like Fluff has a formula for you, but I think you will need to adapt it for each worksheet or else increase the ranges on a single worksheet and just add records to the end as they come in. My KISS approach would be to just make all the ranges and then use each one as needed as you are already using worksheets for each month. Paste in the data and the formulas are already set up. But that's only because I'm really comfortable with doing things that way.

Ron

 

[Fluff]

That formula will always look at the previous months sheet, no need to change anything.

 

[Shiseiji]

I learned something too, didn't know indirect would look at the whole workbook. Never used it.

Ron

 

[RodneyW]

Thanks guys. This gives me enough to work with for now. Lots of time sensitive projects right now. I'll dive in deeper on Monday. Thanks again for the help

 

[Fluff]

Glad we could help & thanks for the feedback.