Fill down formula while incrementing dates

[RussG2]

I have a formula I would like to fill down. It looks like this: =$‘End of Day 08.31.23’.F5
The formula references cell F5 on another sheet. I would like the formula to fill down so the sheet referenced changes as the cells fill down and increments 1 for each weekday. For example the first few cells filled would look like this:
=$‘End of Day 09.01.23’.F5
=$‘End of Day 09.04.23’.F5
=$‘End of Day 09.05.23’.F5
etc. where the date increments one day at a time for weekdays only. Is there a way to do that in LO Calc Mac 7.4.6.2?

I can make the reference to F5 absolute such as $F$5 but I can’t figure out how to increment the dates as shown above.

Thanks.

[karolus]

One could now first question the strategy:
“I absolutely need a new spreadsheet every working day”?
But such queries are boring!

Select the cell which you want to start that nonsense, write only F5 into this cell and execute:

def fetch_cell_from_sheets(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheetnames = doc.Sheets.ElementNames    
    target = doc.CurrentSelection
    cell_name = target.String
    formulas = [(f"='{name}'.{cell_name}",)
                for name in sheetnames
                if name.startswith("End of Day")]
    sheet = target.Spreadsheet
    cursor = sheet.createCursorByRange(target)
    cursor.collapseToSize(1, len(formulas))
    cursor.FormulaArray = formulas

For organizing your python-Scripts you may need apso.oxt from here

[Lupp]

@karolus

One could now first question the strategy…

It’s the tragedy of current spreadsheet software that it’s so hard to find design concepts (or “enhancement requests”) bad enough to make an implementation definitely impossible.

[sokol92]

Put this formula to cell “A2” and fill down.

=INDIRECT("'End of Day " & TEXT(WORKDAY(DATE(2023;8;31);ROW(A2)-ROW($A$2));"MM.DD.YY")&"'.F5")

Remarks.

1. If you need to skip holidays, then add the 3rd parameter of the Workday function.
2. If the starting cell is different from A2, then replace A2 and $A$2 in the FORMULA with the corresponding values.

[RussG2]

@sokol92

Put this formula to cell “A2” and fill down.

=INDIRECT("'End of Day " & TEXT(WORKDAY(DATE(2023;8;31);ROW(A2)-ROW($A$2));"MM.DD.YY")&"'.F5")

Remarks.

1. If you need to skip holidays, then add the 3rd parameter of the Workday function.
2. If the starting cell is different from A2, then replace A2 and $A$2 in the FORMULA with the corresponding values.

Thanks so much for that! It does seem to work. Now I need to study it and figure out what’s going on.