Excel Formula Help - Thanks in advance

[excel_learnerz]

Hello,
I was wondering if the good natured people here at Mr Excel can maybe tell me what im trying to do is feasible or not,
Basically, I have a few different formulas connected to a file on Sharepoint (just index match and vlookup) and they all work fine.
here is one of them below


=IF(A8="","",VLOOKUP(A8,'C:\Users\user1\company\Sales Team - Documents\Assessments\Files\[test_file.xlsx]Sheet1'!A:C,3,FALSE))


Now I am trying to use a value in a different tab that covers the value where the location and file is


so for example Sheet2 A1 = 'C:\Users\user1\company\Sales Team - Documents\Assessments\Files\[test_file.xlsx]Sheet1'


trying to turn the formula into somethinng like below which doesnt work obbviously
=IF(A8="","",VLOOKUP(A8,Sheet2!A1A:C,3,FALSE))
Can anyone else help what it should be,
Thanks in advance

 

[Special-K99]

=IF(A8="","",VLOOKUP(A8,Sheet2!A1A:C,3,FALSE))

A1A:C as typed by yourself is not a valid range

However Sheet2!A:C is.

Is this a typo or exactly what youve entered? If youve entered it like this thats why it doesnt work though I suspect you would have got an error.

 

[excel_learnerz]

Hey,

Thanks for your help, I tried with the indirect function but what I was looking for doesnt seem to be able to be possible. I know the above is an error it was just an idea of what I was trying to show, I have an excel model that needs more improvement so this problem will be pushed back and other ones ill focus on, thanks for your help