Vlookup

[superfb]

Hi guys,

i was wondering, if its possible to alter the elements in the vlookup formula based on a cell

vlookup formula
='[Total Table 45.5 Car Rental Place - Gross 2021.xls]All'!$C$6

Is it possible to change the year via if i have a cell, so if in cell A3 i change it to 2015 will this part of the formula will automatically change to ............Gross 2021.xls]

Be interesting to get some idea is possible?

thanks

 

[Toadstool]

You've a dangling double quote in the middle. Try

=INDIRECT("'["&Q2&F3&".xls]All'!$D$6")

 

[superfb]

You've a dangling double quote in the middle. Try

=INDIRECT("'["&Q2&F3&".xls]All'!$D$6")

the works great....i have another thought....i want to do a vlookup based on text values......but for whatever reason it doesnt get picked up (most likely due to formatting issues?) is there a way to do this? would i need to use find and clean potentially?

it would be handy to use a vlookup but i have two variables like the above indirect formula which is linked to the folder name that the end user can change, and the year

=VLOOKUP(A1,'[Table 4.6a   Bonuses - Excluding overtime 2021.xls]Full-Time'!$A:$Q,3,0)

The two variables are in blue and yellow. Basically the name of S/S and the year in which the end user can enter that in - i hope that makes some sort of sense?

With the first spreadsheet where i am entering the vlookup, if i use upper.....it still isnt being picked up - i used the formula code and they are both 65? so it should be picked up? (unless it sees it as a text? with the green thing on the top right corner) thanks

 

[superfb]

=VLOOKUP(E83,INDIRECT("'["&$Q$3&"]Full-Time'!$A:$Q"),4,FALSE)

I have tried this as a tester - but i am getting a #REF! ERROR!

 

[Toadstool]

You're missing the ".xls". Try

=VLOOKUP(E83,INDIRECT("'["&$Q$3&".xls]Full-Time'!$A:$Q"),4,FALSE)

 

[superfb]

You're missing the ".xls". Try

=VLOOKUP(E83,INDIRECT("'["&$Q$3&".xls]Full-Time'!$A:$Q"),4,FALSE)

Thank you very much

 

[superfb]

You've a dangling double quote in the middle. Try

=INDIRECT("'["&Q2&F3&".xls]All'!$D$6")

Hiya,

apologise the spreadsheet has a abit of a revamp....

The list of file names are in the another tab called"Main Menu" how can i link this Indirect formula to another tab in the workbook, then make the cell reference to another file? thanks

=INDIRECT("'["'&'Main Menu'!$Q$1&"]All'!$D$6")

I have to admit when the formulas are calculating it takes forever due to the 4 thread issue? Is there anything i can do to speed things up instead of changing calculation from auto to manual?