How to get Excel to search multiple files?
March 26, 2012 7:26 AM Subscribe
How do I get Excel to search through multiple files and extract data into a separate worksheet?
I have a folder of separate worksheets which are (more or less) the same format 1 page sheets with various rows and columns of data. They're invoices so the data is generally consistent (Date, Amount, Total etc).
There's a lot of them and I'm trying to get Excel to pull data from various cells in each sheet and index them into a separate index worksheet. I can do something similar (but simpler) with multiple sheets in a workbook, but this one is stumping me.
To make things trickier there are no named cells and sometimes the data is in different rows to the other sheets. However, the rows that are relevant have a text tag a few columns over. For instance, B34 would say 'Tax' and G34 would have the figure. I'm sure there's a method for telling Excel to open a sheet, find those text cells and then deliver the data 2 cols over, but my Googlefu isn't delivering.
I have a folder of separate worksheets which are (more or less) the same format 1 page sheets with various rows and columns of data. They're invoices so the data is generally consistent (Date, Amount, Total etc).
There's a lot of them and I'm trying to get Excel to pull data from various cells in each sheet and index them into a separate index worksheet. I can do something similar (but simpler) with multiple sheets in a workbook, but this one is stumping me.
To make things trickier there are no named cells and sometimes the data is in different rows to the other sheets. However, the rows that are relevant have a text tag a few columns over. For instance, B34 would say 'Tax' and G34 would have the figure. I'm sure there's a method for telling Excel to open a sheet, find those text cells and then deliver the data 2 cols over, but my Googlefu isn't delivering.
Best answer: I don't think you need macros unless you are doing this on a regular basis. Even if you are, as long as the names are the same each time, or are changed in a standard way, you should be fine just using beyond_pink's formula and tweaking the file names.
You need a list in Excel of all of the file names you want to use. If you are using a PC, open a browser that isn't IE and paste the address of the folder into the address bar (if you use IE it will open Windows Explorer and you don't want that). It will show you a list of your files that you can copy and paste into Excel. Delete the columns you don't need and use it to manipulate the formulas. If they change next month, you will probably be able to do a Find/Replace to get the new names.
posted by soelo at 11:05 AM on March 26, 2012
You need a list in Excel of all of the file names you want to use. If you are using a PC, open a browser that isn't IE and paste the address of the folder into the address bar (if you use IE it will open Windows Explorer and you don't want that). It will show you a list of your files that you can copy and paste into Excel. Delete the columns you don't need and use it to manipulate the formulas. If they change next month, you will probably be able to do a Find/Replace to get the new names.
posted by soelo at 11:05 AM on March 26, 2012
P.S. I just realized my formula should have been $B$1:$G$50 (assuming your other file has 50 rows)
posted by beyond_pink at 12:42 PM on March 26, 2012
posted by beyond_pink at 12:42 PM on March 26, 2012
Response by poster: Thanks for the responses. It certainly looks like a step in the right direction.
However, I can't seem to get that formula to work at this end. It either returns a response of zero or flags up an error, regardless of how I tweak it.
posted by panboi at 1:32 PM on March 26, 2012
However, I can't seem to get that formula to work at this end. It either returns a response of zero or flags up an error, regardless of how I tweak it.
posted by panboi at 1:32 PM on March 26, 2012
In case you are copying the formula as written, remove the spaces after the commas.
Try using the formula builder in Excel: put your cursor in the cell, hit the equals sign and then click on VLOOKUP if it shows up or hit the down arrow if another formula shows up.
Lookup_value should be "tax" with the quotes. Click in the box next to Table_array and then highlight the columns you are using starting with the column that says "tax" - it must be the first column in the range. Col_index_num is how many columns it should move over, so from B to G is B,C,D,E,F,G so 6. Range_lookup is optional, but put FALSE in there.
Lastly, make sure the cell actually says "tax" and doesn't have any spaces or other punctuation. Actually type "tax" into it to test if that fixes the formula.
posted by soelo at 2:56 PM on March 26, 2012
Try using the formula builder in Excel: put your cursor in the cell, hit the equals sign and then click on VLOOKUP if it shows up or hit the down arrow if another formula shows up.
Lookup_value should be "tax" with the quotes. Click in the box next to Table_array and then highlight the columns you are using starting with the column that says "tax" - it must be the first column in the range. Col_index_num is how many columns it should move over, so from B to G is B,C,D,E,F,G so 6. Range_lookup is optional, but put FALSE in there.
Lastly, make sure the cell actually says "tax" and doesn't have any spaces or other punctuation. Actually type "tax" into it to test if that fixes the formula.
posted by soelo at 2:56 PM on March 26, 2012
Response by poster: OK, the 'FALSE' element certainly makes a difference and the point about the text element needing to be precise. So I have it working now. Thanks for the advice so far :)
The only tricky thing to solve is taking the file names (BTW soelo, that was a great suggestion as to how to grab all the file names in one go!) and populating those into the formula down the sheet. I can have the formula refer to the file names that are now in a handy column, but it won't read them as paths. There's a prompt for me to insert the file from a pop-up box, but that kind of defeats the object of the exercise.
posted by panboi at 3:28 PM on March 26, 2012
The only tricky thing to solve is taking the file names (BTW soelo, that was a great suggestion as to how to grab all the file names in one go!) and populating those into the formula down the sheet. I can have the formula refer to the file names that are now in a handy column, but it won't read them as paths. There's a prompt for me to insert the file from a pop-up box, but that kind of defeats the object of the exercise.
posted by panboi at 3:28 PM on March 26, 2012
Any way you could copy the sheets into the same workbook? It's shorter to refer to another sheet than it is to refer to another file. I can't take credit for the file name list method, but I can't find the page that I found it on either.
posted by soelo at 7:47 PM on March 26, 2012
posted by soelo at 7:47 PM on March 26, 2012
If you have a list of filenames, as soelo suggested, then you can use INDIRECT to populate the formula with different filenames easily. E.g. for a filename in Cell A1, the formula above could be written as
=VLOOKUP("tax", INDIRECT"[" & A1 & "]Sheet1!$B$1:$C$50" ), 6)
INDIRECT basically treats a string as a worksheet reference, so you can join the filename to another string (e.g. the range), and get a reference to use in a VLOOKUP.
For a list of files in Column A, drag down. I think this only works if the files are open though, Excel doesn't follow INDIRECT links into closed workbooks. You can just select all the files in explorer, drop them on Excel, then paste special as values.
posted by Boobus Tuber at 4:11 PM on March 28, 2012
=VLOOKUP("tax", INDIRECT"[" & A1 & "]Sheet1!$B$1:$C$50" ), 6)
INDIRECT basically treats a string as a worksheet reference, so you can join the filename to another string (e.g. the range), and get a reference to use in a VLOOKUP.
For a list of files in Column A, drag down. I think this only works if the files are open though, Excel doesn't follow INDIRECT links into closed workbooks. You can just select all the files in explorer, drop them on Excel, then paste special as values.
posted by Boobus Tuber at 4:11 PM on March 28, 2012
« Older Help me find this electronics/computer magazine... | Avoiding confusion with superscript/subscript... Newer »
This thread is closed to new comments.
=VLOOKUP("tax", [myotherfile.xlsx]Sheet1!$B$1:$C$50, 6)
posted by beyond_pink at 7:37 AM on March 26, 2012 [1 favorite]