Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flexible formula for linking to externa Files
hi,
I have a file that has (for simplicity) 5 columns: Dept, Class, Style, Color, Units Ordered The Units Ordered column should be a formula to a file named C:\Deptxx, with the xx being a two-digit dept number (e.g. C:\dept10, C:\dept12, C:\dept15 etc) so based on the two digits keyed in column 1 (Dept) the Units ordered column should be a vlookup to file C:\deptxx. so if someone typed 10 for dept number the formula is smart enough to look in c:\dept10. do i do some concatanation to get this done or is there a better way? Thanks in advance for any help! Tami |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flexible formula for linking to externa Files
So if your lookup value was in D1 and the cell with the two-digit
number was in A1, it would go something like: =VLOOKUP(D1,"C:\Dept"&A1&" the\rest\of\your\link!Range",2,FALSE) If you enter 10, this should concatenate to =VLOOKUP(D1,"C:\Dept10 the \rest\of\your\link!Range",2,FALSE) --JP On Feb 2, 1:11*pm, Tami wrote: hi, I have a file that has (for simplicity) 5 columns: Dept, Class, Style, Color, Units Ordered The Units Ordered column should be a formula to a file named C:\Deptxx, with the xx being a two-digit dept number (e.g. C:\dept10, C:\dept12, C:\dept15 etc) so based on the two digits keyed in column 1 (Dept) the Units ordered column should be a vlookup to file C:\deptxx. *so if someone typed 10 for dept number the formula is smart enough to look in c:\dept10. * do i do some concatanation to get this done or is there a better way? Thanks in advance for any help! Tami |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Flexible formula for linking to externa Files
The function you'd want to use is =indirect().
But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. === If you have trouble getting to the site, then search google for indirect.ext. I found this alternative site: http://download.cnet.com/Morefunc/30...-10423159.html I didn't look to see if it was the most current version. I'd check the original site every so often to see if it's working. Tami wrote: hi, I have a file that has (for simplicity) 5 columns: Dept, Class, Style, Color, Units Ordered The Units Ordered column should be a formula to a file named C:\Deptxx, with the xx being a two-digit dept number (e.g. C:\dept10, C:\dept12, C:\dept15 etc) so based on the two digits keyed in column 1 (Dept) the Units ordered column should be a vlookup to file C:\deptxx. so if someone typed 10 for dept number the formula is smart enough to look in c:\dept10. do i do some concatanation to get this done or is there a better way? Thanks in advance for any help! Tami -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Files | Links and Linking in Excel | |||
Need formula help linking closed excel files | Excel Discussion (Misc queries) | |||
Linking Files | Excel Discussion (Misc queries) | |||
need flexible tool to work with flat files - Excel No Good | Excel Discussion (Misc queries) | |||
I NEED A FLEXIBLE FORMULA | Excel Worksheet Functions |