# Help with making a Formula Dynamic

#### Kayslover

##### Board Regular
Hi all,

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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### arthurbr

##### Well-known Member
Try
Excel Formula:
``=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

##### Board Regular
arthurbr,

Firstly, thank you for your time so far.

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

Rich (BB code):
``````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!!!!

Replies
3
Views
209
Replies
9
Views
73
Replies
10
Views
243
Replies
1
Views
146
Replies
3
Views
98

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,842
Messages
5,766,738
Members
425,376
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back