Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I use vlookup to point to an external file that changes nam
I want my file to use vlookup and pull data from the current worksheet but I
also want to copy the formula to subsequent columns which represent dates (week ending... ie. 010705, 011405, 012105, etc.) throughout eternity without having to manually update the formula. The File names 010705... will be in a centralized directory and contain data pertaining to that week and I want it to be returned to the master spreadsheet. When I copy the vlookup formula I want it to automatically change the file it looks in to correspond with pertinenent weekly data. Thanks for your help. |
#2
|
|||
|
|||
Aschaney wrote...
I want my file to use vlookup and pull data from the current worksheet but I also want to copy the formula to subsequent columns which represent dates (week ending... ie. 010705, 011405, 012105, etc.) throughout eternity without having to manually update the formula. The File names 010705... will be in a centralized directory and contain data pertaining to that week and I want it to be returned to the master spreadsheet. When I copy the vlookup formula I want it to automatically change the file it looks in to correspond with pertinenent weekly data. If once entered these formulas wouldn't change, then a 3-step process will be the quickest way to do this. 1. Create text formulas that will produce results that will look like the formulas you want. If the 7-Jan-2005 formula would be in cell C5, something like C5: ="=VLOOKUP(X99,'[foobar.xls]"&TEXT(DATE(2005,1,7)+7*(COLUMN()-3),"MMDDYY") &"'!C3:J1002,6,0)" Fill right as needed. 2. Select all these cells, copy, and paste special as values on top of them to change these formulas to text constants. 3. With these cells still selected, Edit Replace, replacing all = with =. This may seem like a do-nothing operation, but it has the effect of re-entering all these cells, which thus enters them as formulas. |
#3
|
|||
|
|||
We have considered using the replace function as a last option as there are
infinite weeks in time and we do not want to have to replace the lookup "table" value in each formula. We would have the file name statically placed in the column above the function and that is the file name we want the vlookup to reference when copied, even if it is a new file name in the column to the right. "Harlan Grove" wrote: Aschaney wrote... I want my file to use vlookup and pull data from the current worksheet but I also want to copy the formula to subsequent columns which represent dates (week ending... ie. 010705, 011405, 012105, etc.) throughout eternity without having to manually update the formula. The File names 010705... will be in a centralized directory and contain data pertaining to that week and I want it to be returned to the master spreadsheet. When I copy the vlookup formula I want it to automatically change the file it looks in to correspond with pertinenent weekly data. If once entered these formulas wouldn't change, then a 3-step process will be the quickest way to do this. 1. Create text formulas that will produce results that will look like the formulas you want. If the 7-Jan-2005 formula would be in cell C5, something like C5: ="=VLOOKUP(X99,'[foobar.xls]"&TEXT(DATE(2005,1,7)+7*(COLUMN()-3),"MMDDYY") &"'!C3:J1002,6,0)" Fill right as needed. 2. Select all these cells, copy, and paste special as values on top of them to change these formulas to text constants. 3. With these cells still selected, Edit Replace, replacing all = with =. This may seem like a do-nothing operation, but it has the effect of re-entering all these cells, which thus enters them as formulas. |
#4
|
|||
|
|||
Aschaney wrote...
We have considered using the replace function as a last option as there are infinite weeks in time and we do not want to have to replace the lookup "table" value in each formula. We would have the file name statically placed in the column above the function and that is the file name we want the vlookup to reference when copied, even if it is a new file name in the column to the right. .... There aren't an infinite number of columns or rows in Excel worksheets, so you're obliged to work within constraints anyway. Also, if you're dealing in chunks of weeks, it's quite likely you'll be using something other than a spreadsheet in 10-20 years anyway. Be that as it may, you're now mentioning 'file name' when you had mentioned 'worksheet' in your original posting. These terms have precise meanings, so you need to try to use them correctly. workbook = file worksheet is one 'tab' within a workbook or file I'll assume you do mean workbook filename, and that you misused the term 'worksheet' previously. You're also misusing the term 'column'. I can understand 'column to the right', but not 'in the column above'. Do you mean 'in the row above'? Do you have filenames in both the row above and the column to the right of the cell in which you're trying to enter the VLOOKUP formula? If so, which are you trying to use? If you have different static workbook filenames in some range of cells, and you want a corresponding range of VLOOKUP calls using those filenames in external references, and *IF* those workbooks would *ALL* be open in the same Excel session, then you could use the INDIRECT function. =VLOOKUP(whatever, INDIRECT("'["&ReferenceToCellContainingFilename&"]Some Worksheet'!X99:Y500"), 2,0) If your files won't all be open, see option 4 in the following linked, archived message. http://groups-beta.google.com/group/...443753560f0075 (or http://makeashorterlink.com/?F2993260A ). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP from a file on another server | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
File asks to update when no links were created | Links and Linking in Excel | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) | |||
VLOOKUP not working | Excel Worksheet Functions |