I can paste a formula to try and explain and you may be able to get it from the quotes below.
and where its meant to go and what the actual file names are. I think i've worked it out, i just need to format it so the formula accepts it and dont give an error so the function works as intended. I think I've got something like a sympbol missing or in the wrong place (could be to do with the docuemnt name and sheets have spaces in and need more punctuation?)
in my examples working on Row2 for this formula
Current Formula in cell A2 is
Quote:
|
=VLOOKUP(P2,'[2015 Cordell Valuation Lookup Tables.xlsx]AMS Outage October 2015'!$A:$I,5,FALSE)
|
im cell BK2 i have the words
I'd like to use indirect to do this
Quote:
|
=VLOOKUP(P2,'[2015 Cordell Valuation Lookup Tables.xlsx]REPLACE THIS PART WITH THE VALUE IN BK2 BY USING INDIRECT SO IT LOOKS UP ANOTHER SHEET IN ANOTHER DOCUMENT'!$A:$I,5,FALSE)
|
The full file name location of the sheet i want indirect to look at is
Quote:
|
W:\Concord\ICM\Costs\78373 - Cordell\2015\2015 Cordell Valuation Lookup Tables.xlsx
|
Looking at other sites and using your help I came up with
Quote:
|
=VLOOKUP(P2,INDIRECT("'[2015 Cordell Valuation Lookup Tables.xlsx]"&BK2&"'!$A:$I,5,FALSE)
|
which dont work - I think I've entered it slighlty wrong.
I got the idea to use the above formula (that dont function yet) from this on another site when reading further into it
Quote:
For the Indirect() formula to work on an external file, the other file must be open. Then you can use something like
=VLOOKUP(A14,Indirect("'[excel1.xlsm]"&A5&"'!$A:$U"),3,FALSE)
|
Is that better for you to see what I need to do. I dont mind putting the actual full filename and path in for the document and thats why have included it in the quotes thanks again
Also on a side note, ive tried googling but cant see an answer
What are the & symbols for in excel? Im unsure on their purpose and not seen them before?
thanks for your continued help even though its probally bit frustrating for you guys