Dynamic workbook reference based on current date

[barmstr6]

I want to take the current title of a workbook that has a date in the title and have it reference a previous days workbook that also has a date in it.

For example, I have a workbook titled 9/15/2023 Data Set. Within that workbook I want it to sumif a range in a workbook titled 9/14/2023 Data Set and have this continuous update as I create new daily Data Set workbooks each day so I dont have to change the reference every time a new workbook is created.

 

[Micron]

If I understand your post, you could use a vba function in your formula to get the date value portion from the workbook name and have the function add 1 day (or is it subtract - I'm not sure which it is you need). So 09/15/2023 could become 09/16/2023 or 09/14/2023 depending whether you add or subtract.

 

[6StringJazzer]

For example, I have a workbook titled 9/15/2023 Data Set.

That is an illegal file name. Please give a real example.

 

[barmstr6]

That is an illegal file name. Please give a real example.

09-15-23 Data Set

 

[6StringJazzer]

This function will accept the name of a workbook in the form "mm-dd-yyyy Data Set" and return the name of the workbook for the previous day.

Public Function PrevDayWB(BaseWBName As String) As String

   Dim OriginalDate As Date
   
   OriginalDate = DateValue(Mid(BaseWBName, 1, 10))
   
   PrevDayWB = Format(OriginalDate - 1, "mm-dd-yyyy") & " Data Set"

End Function

 

[barmstr6]

This function will accept the name of a workbook in the form "mm-dd-yyyy Data Set" and return the name of the workbook for the previous day.

Public Function PrevDayWB(BaseWBName As String) As String

   Dim OriginalDate As Date
  
   OriginalDate = DateValue(Mid(BaseWBName, 1, 10))
  
   PrevDayWB = Format(OriginalDate - 1, "mm-dd-yyyy") & " Data Set"

End Function

I'm not very knowledgeable in VBA coding but where do I take this info and put it? I tried putting it into a macro to run but didn't work or maybe I messed up.

 

[6StringJazzer]

You'll have to give me more information about your overall situation. It sounds like you just have a vague idea of what you need to do rather than an overall approach.

I want to take the current title of a workbook that has a date in the title

How do you want to take it? Where is this current title stored so that formulas or code can reference it?

and have it reference a previous days workbook that also has a date in it.

How to do you want reference the other workbook? If in a formula, what is the formula? You mentioned SUMIF but please show the actual formula.

It is possible to do this using INDIRECT without using VBA but that means the file you want to reference in the SUMIF formula must be open. If that is not useful then we'll have to use VBA. I can give you a more detailed description of what to do but the one thing that's required is where is the name of the current workbook that you're starting with.