Make the vlookup function dynamic for different computers

https://youtu.be/gCvlAtUIaZc

I am doing exactly what is being done in the video in the link above. The problem I have though is, this will only work on a specific computer because of the directory. What I need to do is transfer both the excel files to many computers. So when I do that and open the file that has vlookup formulas embedded on a different computer, it just says #REF. It is not dynamic. How do I make it so that I would never have to worry about entering/changing formulas on every computer?

=VLOOKUP(A3,'C:\Users\Ted\Desktop\Files\[myfile.xlsx]myfile'!$A:$C,3,0)

I don't understand what you have there. Please look at the formula I have and let me know how I can modify that.

Originally Posted by BoredWorker

For the reason of cross the files function work is, Excel is open the file in the computer by the address have.

Untest,
It may be work when the file is saved in the same place in other computers.
then use the indirect functions.

combine in a folder, and sent to them, then use cell function, in a random place use this formula, it create a path to anther workbook.
=REPLACE(CELL("filename"),SEARCH("folder name"&"/",CELL("filename"))+count of folder name+1,100,"["&other workbok filename[/COLOR]"]")

then use INDIRECT function,
example, (given above formula in A1)
=indirect("'"&"A1"&"sheet1'!A1)+A2

"folder name", the folder that you sent to other.
other workbok filename, include the .xls
"count of folder name", can use the LEN function to help if the filename is too long.
the CELL("filename") is the actual formula, don't change!!

I don't understand what you have there. Please look at the formula I have and let me know how I can modify that.

Originally Posted by Special-K

Whats your formula exactly?

=VLOOKUP(A3,'C:\Users\Ted\Desktop\Files\[myfile.xlsx]myfile'!$A:$C,3,0)

Originally Posted by BoredWorker

Step 1, create a folder, name: Lazy
put in all your file that linked together

Step 2,
in your existing file, where you put this formula,

in Z1 put in this,
=REPLACE(CELL("filename"),SEARCH("Lazy"&"/",CELL("filename"))+5+1,100,"["&myfile.xlsx&"]")

Step 3,
replace your formula to
=VLOOKUP(A3,indirect("'"&$Z$1&"myfile'!$A:$C",3,0)

Step 4,
Sent the whole folder to them.

Please test on your own... i only have one computer..... cannot test
PS: seldom email able to sent folder, you may use google drive or other.

Advice: better to include that sheet of data in one file. this might cause lag for few minute when too many vlookup function is use together with this case issue.

Your step 3 is incorrectly formatted. There are too many arguments in the function.

Originally Posted by BoredWorker

Sorry for the basic mistake...

=VLOOKUP(A3,indirect("'"&$Z$1&"myfile'!$A:$C"),3,0)

It doesn't even work on my own computer. It says #VALUE!

Originally Posted by BoredWorker

Can it know the result of Z1 is look like this?

'C:\Users\Ted\Desktop\Files\[myfile.xlsx]

The result in cell Z1 is also #VALUE!

Originally Posted by BoredWorker

ok, now the reason that cause vlookup function failed is the value Z1 already have error.

Sorry for another mistake.....

=REPLACE(CELL("filename"),SEARCH("Lazy"&"\",CELL("filename"))+4+1,100,"["&myfile.xlsx&"]")

And the result should be

C:\Users\Ted\Desktop\lazy\[myfile.xlsx]

Now it says #NAME?

Indirect does not work on closed workbooks.

I would suggest instructing the end users to not put the files on their desktop.
Instead put them in a designated folder on the hard drive, so it's the same for everyone.
Like C:\ExcelFiles\filename.xls

Originally Posted by BoredWorker

Oppss.....

another mistake, I found out already in past 10 minute and forget to put it in....

=REPLACE(CELL("filename"),SEARCH("Lazy"&"\",CELL("filename"))+4+1,100,"["&"myfile.xlsx"&"]")

It's almost working. It says C:\Users\Ted\Desktop\lazy\[l[myfile.xlsx]. I see an error in there. Why is there an "l" before myfile?

Originally Posted by BoredWorker

I cann't see any mistake here.

You try to paste again the formula.

=REPLACE(CELL("filename"),SEARCH("Lazy"&"\",CELL("filename"))+4+1,100,"["&"myfile.xlsx"&"]")

Now the problem is in the cells. Does this look right? Because it is giving me an error #REF!
=VLOOKUP(A3,INDIRECT("'"&$Z$1&"myfile'!$A:$C"),3,0)

And could you also tell me what 4+1,100 is? How are you getting that?

Originally Posted by BoredWorker

4+1 in the formula is in the

=replace(,"here",,)

the search is allocate the number at C:\Users\Ted\Desktop\"HERE"lazy\[myfile.xlsx]
after add in the 4+1,
it turn allocate at C:\Users\Ted\Desktop\lazy\"HERE"[myfile.xlsx]

And 100 is
=replace(,,"here",)
to replace how many character after the allocate point.
100 is to avoid the character of the file is too long.



try to open the located workbook, the reason is said by Jonmo1. you may try to google about it.

If I place the files in the C: drive, for example, then according to Jonmo1, I am placing them separately and not in the folder named "Lazy". Now, the Z1 formula will require changes, which I don't want. I actually want all the files in a folder.

Originally Posted by BoredWorker

ok, one by one,

first, did the vlookup function well when you open both workbook?

Second,

I just tell you the reason, the indirect cannot function to a closed workbook, not solution.....

Yes, I did everything as you told me to do it. The Z1 cell works, but the other cells says #REF!

Originally Posted by BoredWorker

Sorry, I just have a test just now....

My here also not function at all.....

Sorry for waste your time.....

It's alright. You don't have to be sorry about anything.
I appreciate you spending the time to help me out. Thanks.

However, do you not know any other way of getting this solved?

Originally Posted by Jonmo1

Indirect does not work on closed workbooks.

I would suggest instructing the end users to not put the files on their desktop.
Instead put them in a designated folder on the hard drive, so it's the same for everyone.
Like C:\ExcelFiles\filename.xls

Can you help me out?
BoredWorker helped as much as he could, but we weren't successful at the end.

Originally Posted by BoredWorker

At I said in beginning, just keep all data in one file......
it can be put different sheets to keep thing simple.

when the situation that the file is too large to sent by email, try use google drive.

or, after you vlookup, copy and paste as value to cover the answer.
the result is still can view, but it not convince to other that is the result from vlookup...LOL

or,

and jonmo1 suggestion is let other who use the file put in a selected location, like save in C:\ExcelFiles\filename.xls
your part
step 1, open a folder to local hard disk, create a file name call Excelfiles
step 2, save the file in and open the file do the vlookup.
step 3, sent the receiver part below to them.

Receiver part
step 1, open a folder to local hard disk, create a file name call Excelfiles
step 2, move the file receive to this folder
step 3, and use as normal.

Untested as well...

I can't keep all the sheets in one file because one of the files gets updated everyday. Anyways, no problem. I will figure something out.

Originally Posted by BoredWorker

yes, one more things,

did you ever use Google sheet?

It mostly similar to excel, but it can share to other to edit or view only permission.

weakness,
if personal ID, the storage is 5 GB. the upgrade of storage is need to pay.

I am not gonna worry about all that.

Originally Posted by ted1234

I am not gonna worry about all that.

https://www.excelforum.com/excel-pro...formation.html
Can you help me out?