Hi all,
I have an Excel Template that has the following formula in cell C11 in sheet called Invoices
The same formula also exists in cells D11 to H11, but the field number for the lookup is different.
The problem I am faced with is that this workbook gets generated from a Template every year.
As we are in 2021, the formula refers to '[Invoices Issued.xlsx]2021.
However when we move to 2022, 2024… I would like the reference to 2021 to change to 2022, 2023 automatically.
The year that the workbook will be created for exist in sheet called Formula and the year is in Cell A1.
The only other way that I can think of achieving what I want is once the workbook has been created, to then select all sheets that will have reference to the previous year and do a replace all with the current year.
Would appreciate any assistance given.
I have an Excel Template that has the following formula in cell C11 in sheet called Invoices
Rich (BB code):
=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,10,FALSE)
The same formula also exists in cells D11 to H11, but the field number for the lookup is different.
The problem I am faced with is that this workbook gets generated from a Template every year.
As we are in 2021, the formula refers to '[Invoices Issued.xlsx]2021.
However when we move to 2022, 2024… I would like the reference to 2021 to change to 2022, 2023 automatically.
The year that the workbook will be created for exist in sheet called Formula and the year is in Cell A1.
The only other way that I can think of achieving what I want is once the workbook has been created, to then select all sheets that will have reference to the previous year and do a replace all with the current year.
Would appreciate any assistance given.