Help with making a Formula Dynamic

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Dec 8, 2006
Messages
2,336
Office Version
  1. 2010
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
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
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:-

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!!!!
 
Master Excel Bundle

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.

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top