Excel links 1

[jisoo22]

Hello!

I actually have two questions about linking within cells of an excel 2000 spreadsheet:

#1 Is there any way I can make hyperlinks within a spreadsheet relative or generic? For instance, if I have a hyperlink that links to another workbook's spreadsheet, it wouldn't do me any good if I put these two workbooks in another computer because the links are still set to the addresses on my own computer.

#2 I have a form where if I type in an item's serial number in one cell, the item's description is pulled from another workbook's spreadsheet and placed into the next cell over. Unfortunately all I get is a big, fat, "0" for a description when I put in the serial number. The cell's code/logic is as follows:

=IF(ISNA(VLOOKUP(A40,'C:\WINDOWS\Desktop\[master parts price list.xls]s189975'!$B$2:$E$8116,2,FALSE)),"",VLOOKUP(A40,'C:\WINDOWS\Desktop\[master parts price list.xls]s189975'!$B$2:$E$8116,2,FALSE))

Does anyone have any idea what's wrong?

Thanks,
Jisoo22

P.S. In the 2nd workbook, all the descriptions are in the "D" column. Just in case I hope this helps!

 

[JVFriederick]

Hi jisoo22
Q #2)
Try using the lookup without checking for a number:

=VLOOKUP(A40,'C:\WINDOWS\Desktop\[master parts price list.xls]s189975'!$B$2:$E$8116,3,FALSE)

If your descriptions are in D, and the lookup range is B:E, then the column value should be 3 (the first column is 1 in Excel, 0 in Lotus)

Q #1) Let me know if you still need help with this one.

 

[jisoo22]

Thanks for the answer to question #2 =) It's a real big help! I still need help on question #1 though. The main problem is that there's two workbooks with sheets that refer to each other through links/hyperlinks. What I need is to make them relative or generic so I can put these workbooks on any computer without having to worry about changing the hyperlinks each time. I.E. hyperlink = "C:\windows\desktop\workbook1.xsl". Need this changed to hyperlink = "workbook1.xsl" so that there isn't really any reliance to a specific address.

Thanks!
Jisoo22

 

[JVFriederick]

Q#1) I've always had trouble getting Excel to "remember" the "generic" links. It seems to often save the old drive and pathnames within the link. I've given up and now require both files to be in the same directory or use a variable based upon the first file opened. Sounds like you want the flexibility to place the 2 files anywhere on a PC and for the first file to always find the second?

To force Excel to set the current directory based upon the file opened, define a range name CurrentDirectory and enter the following formula into cell A1:
=MID(CELL("filename",A2),1,FIND("[",CELL("filename",A2))-2)

Now enter the following code into Sub Autpen(), which is the macro that runs just after file is opened :

TempVar$ = Range("CurrentDirectory&quot
ChDrive Left(TempVar$, 1) ' Set current drive.
ChDir TempVar$ ' Set Current directory

Too complicated? Maybe. It's the only way I've found to ensure the links stay the way I want.

Alternatively, if you always open the other file (the one with descriptions), the links seem to stay in good shape. Better yet, combine the two files together. I assume there must be a reason why this cannot be done, such as another user needs to edit the file with descriptions.

Hope this helps. If you don't like any of these ideas, tell me more about the 2 files you are working with.

 

[jisoo22]

Cool, thanks for the help with the hyperlinks! There was one snag, TempVar$ = Range("CurrentDirectory&quot wouldn't fly but it worked when I changed "CurrentDirectory" to "A1". It works perfectly now! Whatever works is great hehe =) Still have one more thing though, when I put in the "vlookup" statement that you suggested, I no longer receive a "0" but rather "#N/A". That wigged me out, do you have any ideas why that might be?

Thanks!
Jisoo22

P.S. You've been a huge help! Voted for you twice already lol

 

[JVFriederick]

Thanks. Yes, I meant for the CurrentDirectory to be in the same place as the formula, but wasn't very clear. Glad you fixed it on your own.

Far as the VLOOKUP, that usually means the value was not found. Check to see that it exists. Step 2 is to see if it exists as a string versus a number. One way to fix that is standardize all part numbers as strings, or standardize all lookup values as strings. This can be as simple as typing a ' in front of the value.

Hope this makes sense.