Vba changing the reference column year

[blaisjoel]

I have a spreadsheet table that has columns for the last 10 years. Each new year, these columns are changed to add one year to it. My problem is that I have another workbook table referencing this table with each cell using a formula. I want to be able to change the formula so it references the correct column.

Here is a preview of what the formula looks like.

=LOOKUP(2,1/('ARTICULATED TRUCK AVERAGE HOURS YEARLY.xlsx'!ARTICULATEDTRUCK[2018]<>""),'ARTICULATED TRUCK AVERAGE HOURS YEARLY.xlsx'!ARTICULATEDTRUCK[2018])

Need to change the 2018 to 2019 in the formula.

 

[Trevor G]

Record a macro to use find and replace, that should pick up 2018 and replace with 2019. Then you have the code

 

[blaisjoel]

That's great thinking Trevor. Thanks.
I never noticed Excel gives me the ability to find and replace in formulas.
I did have another solution which would change my code to select last row in column address instead of last row from column header. I prefer using the header.

 

[Trevor G]

Good to read you have a solution that works for you.