change date in formula for each day in the month

[StacieD]

I have a formula to link data from other workbooks. The formula is:

'[SM Daily Workbook 03-01-23 ag tracking.xlsx]24 Hour Staffing'!$D$43
'[SM Daily Workbook 03-01-23 ag tracking.xlsx]24 Hour Staffing'!$I$43
'[SM Daily Workbook 03-01-23 ag tracking.xlsx]24 Hour Staffing'!$O$43

'[SM Daily Workbook 03-08-23 ag tracking.xlsx]24 Hour Staffing'!$D$43
etc.

I need to change the date so that it pulls different cells from each of the daily work workbooks without having to manually type it in for each day of the year. Any help with this?

[6StringJazzer]

What determines the date you want to use?

You can use INDIRECT but that requires the daily workbook to be open, or you will get an error. Otherwise the only other solutions require VBA.