Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP across all sheets in a workbook
what is need is something like:
=VLOOKUP(A2;'Sheet1:Sheet2'!A:G;5;FALSE) but of course it will not work in such way. I jsut need the vlookup to search not in one but ALL sheets and there are around a 1000, so instead of repeating the function =VLOOKUP(A2;Sheet(1)!A:G;5;FALSE) on each sheet i would like the function to go through all sheets at once) Best Regards, Pawel P. (X) is the sheet number :-) |
#2
|
|||
|
|||
Pawel P. wrote...
what is need is something like: =VLOOKUP(A2;'Sheet1:Sheet2'!A:G;5;FALSE) but of course it will not work in such way. I jsut need the vlookup to search not in one but ALL sheets and there are around a 1000, so instead of repeating the function =VLOOKUP(A2;Sheet(1)!A:G;5;FALSE) on each sheet i would like the function to go through all sheets at once) You have 1000 or so worksheets each with 65536 rows of data in columns A through G? Even if each cell ate just one byte of memory, this would require over 450 million bytes. With numeric data, stored as 8-byte reals, you'd blow through real RAM and go well into virtual memory on most current systems. A single such formula would take a long time to calculate. If you really do have this much data, you NEED a database, and you're begging for BIG TROUBLE using Excel (or any other spreadsheet). However, there is a way to do this (but it'll be SLOW if you really do have this much data). Create a list of worksheet names (I'll refer to it as WSLst). Then use the array formula =VLOOKUP(A2;INDIRECT("'"&INDEX(WSLst;MATCH(1; COUNTIF(INDIRECT("'"&WSLst&"'!A:A");A2);0))&"'!A:G ");5;0) |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
Nick Hodge wrote...
sssshhhhh, you might get Aaron back ;-) .... Different newsgroup. You're giving the troll too much credit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy comments to several sheets in a workbook? | Excel Worksheet Functions | |||
copy a workbook from other workbook with lot of sheets wit... | Excel Discussion (Misc queries) | |||
how can i link sheets in my excel workbook | Excel Worksheet Functions | |||
There is no way to view multiple sheets from one workbook | Excel Discussion (Misc queries) | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) |