PDA

View Full Version : Exchange (part of) file name with a cell ref in Vlookup formula?



Eville
07-28-2010, 12:20 AM
Hiya ;)

So far VBA Express helped me dozens of times, hope you experts can help me again with this.
I have been struggling with this for a while now and not sure if it is possible but i give it a shot ^^

At the moment i have this working formula:

=IF(ISNA(VLOOKUP($A$6;[D213.xls]OrderMP3!$C$17:$I$9999;2;FALSE)=TRUE);"";(VLOOKUP($A$6;[D213.xls]OrderMP3!$C$17:$I$9999;2;FALSE)))

While I would like to have this formula (not working atm):

=IF(ISNA(VLOOKUP($A$6;INDIRECT("'C:\Documents and Settings\datasheet\[D213.xls]OrderMP3!'$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("'C:\Documents and Settings\datasheet\[D213.xls]OrderMP3!'$C$17:$I$9999");2;FALSE)))

But with the D213.xls replaced by a cell ref. I would like to make a formula similar like this:

=IF(ISNA(VLOOKUP($A$6;INDIRECT("'C:\Documents and Settings\datasheet\[C2.xls]OrderMP3!'$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("'C:\Documents and Settings\datasheet\[C2.xls]OrderMP3!'$C$17:$I$9999");2;FALSE)))

Where C2 contains the "D213" text.
This way i can make the sheet standard, only the C2 needs a new number and the cells with the formula dont have to be changed.

My other workbook D213.xls is open so indirect function should work?

Can this be done with a formula or does it need VBA? *keeps fingers crossed a formula does the job* easiest to implement into the existing sheet ...

Thx loads in advance!

Best regards,

Eville

Bob Phillips
07-28-2010, 01:20 AM
I am assuing the referenced workbooks will be closed? In which case, no, it cannot be done with formulae, you need VBA in some form.

Eville
07-28-2010, 08:12 AM
I am assuing the referenced workbooks will be closed? In which case, no, it cannot be done with formulae, you need VBA in some form.
Hi ;)
No, both workbooks are opened because both are needed at same time for different purposes.

Bob Phillips
07-28-2010, 08:25 AM
Then the INDIRECT with the book names, NOT the path, should work fine.

Eville
07-28-2010, 09:21 AM
Apart from the fact formula =IF(ISNA(VLOOKUP($A$6;INDIRECT("[D213.xls]OrderMP3!$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("[D213.xls]OrderMP3!$C$17:$I$9999");2;FALSE)))
works now i also would like the part [D213.xls] being replaced by a cell as that cell (C2) contains the file name of the .xls file it refers to in the formula.
I tried to combine cell C2 and another cell that contains .xls text so it reads D213.xls (with =C2&C3) but no succes either.
BTW: removed the path name i assume that both files now need ot be into the same folder to make this work? ><
Thx in advance again ^^
Eville

Edit: trail and error; the formula works untill the D213.xls file is closed. Then #ref! appears. Is it possible to include in the formula that once a cell has data, the data stays fixed?

Bob Phillips
07-28-2010, 09:57 AM
You would use

=IF(ISNA(VLOOKUP($A$6;INDIRECT("["C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("["C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)))

As I said earlier, if they are closed, it ain't going to work without code.

Eville
07-29-2010, 08:20 AM
You would use

=IF(ISNA(VLOOKUP($A$6;INDIRECT("["C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("["C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)))

As I said earlier, if they are closed, it ain't going to work without code.
I tried your suggestion but it gives 'the formula you types contains an error' and lights up the part "["C2 in the first bit. i tried other variations but no joy.

=IF(ISNA(VLOOKUP($A10;[D213.xls]OrderMP3!$C$17:$I$9999;3;FALSE)=TRUE);"";(VLOOKUP($A10;[D213.xls]OrderMP3!$C$17:$I$9999;3;FALSE)))
seems to work pretty well for me atm with D213.xls open or closed both work. even if the D213.xls file is in a different folder. I have not been able to test at work with diff folders but i guess it should work just as well.

Only problem i have atm is replace the part of the formula [D213.xls] by a cell (eg C2) in a way that the formula works.
For me this part is confusing and complicated because i dont understand 100% of every part what the formula does i guess ><

Any other ideas that can help me on my way again?
Much appreciated as always!

Eville ^^

PS: sorry for the slow replies but im on night shifts

Bob Phillips
07-29-2010, 08:44 AM
I missed som &s

=IF(ISNA(VLOOKUP($A$6;INDIRECT("["&C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("["&C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)))

Eville
07-29-2010, 01:12 PM
I missed som &s

=IF(ISNA(VLOOKUP($A$6;INDIRECT("["&C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)=TRUE);"";(VLOOKUP($A$6;INDIRECT("["&C2&C3&"]OrderMP3!$C$17:$I$9999");2;FALSE)))

The formula works wonderfull thx :D
However when i close the D213.xls file all filled values change into #REF! :(
So close but fails at the finish ... is there a way to change the formula so it doesnt have the indirect function? Or does that mean it only works with a closed D213.xls file?
Else maybe it is possible to "fix" the filled values when the D213.xls file gets closed so they dont turn into #REF! values?
Thx again in advance xld, without this forum and your help there is no way i will get this to work :bow:
Eville

Edit: i am aware there is an add-in for indirect.ext and a PULL option but with multiple people on many different PC's working i dont think thats the right option so if possible i like to try find the sollution into a formula.

Bob Phillips
07-29-2010, 02:03 PM
You have a catch-22 I am afraid. You have to use INDIRECT to get part of the reference from a cell, but INDIRECT fails on a closed file.

Your only options are the two that you mention.

Eville
07-29-2010, 02:23 PM
You have a catch-22 I am afraid. You have to use INDIRECT to get part of the reference from a cell, but INDIRECT fails on a closed file.

Your only options are the two that you mention.
was afraid to read this ><
Is there any other sollution?
Maybe it is possible to enclose the add-in into the file, so the add-in runs while the file is open or installed on file open? No idea how to but i read people have done it, not sure how where or when tho.
The D213.xls file is open most the time but i dont want to loose all extracted data as soon as D213.xls closes.

Eville

Bob Phillips
07-30-2010, 12:55 AM
The whole point of having a reference (link) to another file is so that any updates are reflected in the other file. IF you can forego that, you can stick the values, get rid of the formulae, and all is well, just not dynamic.

Other than that, I said ... Your only options are the two that you mention ... and you ask ... Is there any other sollution?. Methinks we go round in circles.

As for the addin in the file, I don't understand what you are getting at here. Addins are separate, they are in their own file.