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

 

[Sufiyan97]

Do you mean like this?

Book5
	A	B	C	D	E
1
2
3	2021
4
5
6				Total Table 45.5 Car Rental Place - Gross 2021.xls]All'!$C$6
7
8
9
Sheet1

Cell Formulas
Range	Formula
D6	D6	=CONCATENATE("Total Table 45.5 Car Rental Place - Gross ",A3,".xls]All'!$C$6")

 

[Toadstool]

Hi Superfb,

Yes, by using INDIRECT but those sheets will also need to be open in Excel or you will get a #REF! error.

Superfb.xls
	A	B
1	Year	INDIRECT
2	2015	This is C6 in Total Table 45.5 Car Rental Place - Gross 2015.xls
3	2021	This cell C6 in Total Table 45.5 Car Rental Place - Gross 2021.xls
4	2020	#REF!
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDIRECT("'[Total Table 45.5 Car Rental Place - Gross "&A2&".xls]Sheet1'!$C$6")

 

[superfb]

Hi Superfb,

Yes, by using INDIRECT but those sheets will also need to be open in Excel or you will get a #REF! error.

Superfb.xls
	A	B
1	Year	INDIRECT
2	2015	This is C6 in Total Table 45.5 Car Rental Place - Gross 2015.xls
3	2021	This cell C6 in Total Table 45.5 Car Rental Place - Gross 2021.xls
4	2020	#REF!
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDIRECT("'[Total Table 45.5 Car Rental Place - Gross "&A2&".xls]Sheet1'!$C$6")

Hi thank you for the reply, unfortunately i want to avoid opening the spreadsheets if possible otherwise will be confusing for the end user!

 

[superfb]

Do you mean like this?

Book5
	A	B	C	D	E
1
2
3	2021
4
5
6				Total Table 45.5 Car Rental Place - Gross 2021.xls]All'!$C$6
7
8
9
Sheet1

Cell Formulas
Range	Formula
D6	D6	=CONCATENATE("Total Table 45.5 Car Rental Place - Gross ",A3,".xls]All'!$C$6")

Although this works great, but it doesnt return the value from the other workbook, it rather shows the text which is the problem i am having

 

[Fluff]

To link to an external workbook using a "variable" you will need to use Indirect, so you have a choice.
a) hardcode the entire workbook/sheet name, which works on closed workbooks.
b) use a cell as a variable along with indirect & have those workbooks open.

 

[superfb]

Hi Superfb,

Yes, by using INDIRECT but those sheets will also need to be open in Excel or you will get a #REF! error.

Superfb.xls
	A	B
1	Year	INDIRECT
2	2015	This is C6 in Total Table 45.5 Car Rental Place - Gross 2015.xls
3	2021	This cell C6 in Total Table 45.5 Car Rental Place - Gross 2021.xls
4	2020	#REF!
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDIRECT("'[Total Table 45.5 Car Rental Place - Gross "&A2&".xls]Sheet1'!$C$6")

I guess if i have a macro to open the list of filebooks in col h, and then closed - this would hugely compliment this

 

[superfb]

To link to an external workbook using a "variable" you will need to use Indirect, so you have a choice.
a) hardcode the entire workbook/sheet name, which works on closed workbooks.
b) use a cell as a variable along with indirect & have those workbooks open.

Is it possible to have the file name in the formula to be cell referenced?

 

[Fluff]

Only with indirect, which means the other workbook must be open.

 

[superfb]

Only with indirect, which means the other workbook must be open.

Im beginning to understand the importance of keeping the workbook open, as otherwise it goes to Ref error......

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

When i try and make the file name a variable it highlights this bit ".xls]All'

Am i missing something obvious?