Help with making a Formula Dynamic

[Kayslover]

Hi all,

I have an Excel Template that has the following formula in cell C11 in sheet called Invoices

=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.

 

[arthurbr]

Try

=VLOOKUP(B11,INDIRECT('[Invoices Issued.xlsx]"&A1&"'!$A$3:$J$152",10,FALSE)

1. That is assuming : you will also have 152 rows every year
2. You are OK with loosing the data form the previous year
3. You don't mind possible slowdown of your wbk ( INDIRECT is a volatile function) if you have many of those formulas

 

[Kayslover]

arthurbr,

Firstly, thank you for your time so far.

I answer your questions:-

1. The reference to 152 may change. If that is the case, this will be applied to the template and therefore when the workbook is created it will have the increased range.
2. I don’t understand your comments about losing the data for previous years. Please elaborate.
3. The Indirect formula will exist in cells (C11:H299). It is possible that this range could expand over time. With the current range, do you think that there will be a big slowdown?

Can you please explain the &A1& in the formula. The reason why I ask is that the current year is in sheet formula A1

I have inserted your formula in sheet Invoices cell C11 and D11 and it doesn't bring anything back. Additionally, when I look at the formula in the formula bar it looks like the following:-

Yearly Club Accounts 



=VLOOKUP(B11,INDIRECT('[Invoices Issued.xlsx]"&A1&"'!$A$3:$J$152",10,FALSE)

Seems that there are a couple of empty lines before the actual formula!!!!