Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup across multiple worksheets
I want to create a summary sheet that will lookup a particular cells value on
multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#2
|
|||
|
|||
There is no built in lookuop that will work over multiple sheets, I would
recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#3
|
|||
|
|||
Thank you, Peo.
I will give this a try. I'll let you know! "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#4
|
|||
|
|||
Good morning, Peo.
I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0),2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M305'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREED('G:\TAX\2004\ZZ ZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#5
|
|||
|
|||
This works for me
=VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#6
|
|||
|
|||
I tried this and discovered a few problems that were self inflicted. I
rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#7
|
|||
|
|||
I don't see any problems with that, if you would have asked that is what I
would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#8
|
|||
|
|||
Lookup across multiple worksheets
Can you only use this as a function if you have downloaded something??
"Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#9
|
|||
|
|||
Lookup across multiple worksheets
There are some fairly complex workarounds without using the add-in but you
need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#10
|
|||
|
|||
Lookup across multiple worksheets
I need to be able to find a given text from workbook1 from any worksheet in
workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#11
|
|||
|
|||
Lookup across multiple worksheets
Are all the tables in the sheets you want to lookup of the same size like
Sheet2!A1:C100 Sheet3!A1:C100 ? -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... I need to be able to find a given text from workbook1 from any worksheet in workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#12
|
|||
|
|||
Lookup across multiple worksheets
yes they are
"Peo Sjoblom" wrote: Are all the tables in the sheets you want to lookup of the same size like Sheet2!A1:C100 Sheet3!A1:C100 ? -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... I need to be able to find a given text from workbook1 from any worksheet in workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#13
|
|||
|
|||
Lookup across multiple worksheets
Ok, first make a list with all sheet names, i.e. if your sheets are named
Sheet1 Sheet2 Sheet3 etc make a list by typing all sheet names into that list, assume you type the list into K1:K15, now select K1:K15 with the mouse and type a name into the name box (name box is above column A's header) create this list in workbook1, name it for instance MySheets Assume that in workbook1 the lookup values start in A1 in Sheet1 in any cell in workbook1 sheet1 put =VLOOKUP(A1,INDIRECT("'[workbook2.xls]"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT(" '[workbook2.xls]"&MySheets&"'!A1:A50"),A1)0,0))&"'!A:B"),2,0) entered with ctrl + shift & enter will lookup what's in cell A1 in sheets 1 15 in workbook2, note that the workbooks need to be open -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... yes they are "Peo Sjoblom" wrote: Are all the tables in the sheets you want to lookup of the same size like Sheet2!A1:C100 Sheet3!A1:C100 ? -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... I need to be able to find a given text from workbook1 from any worksheet in workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup across multiple worksheets
Thank you so much for your help!! I finally got it to work!! Thanks again!!
"Peo Sjoblom" wrote: Ok, first make a list with all sheet names, i.e. if your sheets are named Sheet1 Sheet2 Sheet3 etc make a list by typing all sheet names into that list, assume you type the list into K1:K15, now select K1:K15 with the mouse and type a name into the name box (name box is above column A's header) create this list in workbook1, name it for instance MySheets Assume that in workbook1 the lookup values start in A1 in Sheet1 in any cell in workbook1 sheet1 put =VLOOKUP(A1,INDIRECT("'[workbook2.xls]"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT(" '[workbook2.xls]"&MySheets&"'!A1:A50"),A1)0,0))&"'!A:B"),2,0) entered with ctrl + shift & enter will lookup what's in cell A1 in sheets 1 15 in workbook2, note that the workbooks need to be open -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... yes they are "Peo Sjoblom" wrote: Are all the tables in the sheets you want to lookup of the same size like Sheet2!A1:C100 Sheet3!A1:C100 ? -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... I need to be able to find a given text from workbook1 from any worksheet in workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup across multiple worksheets
Thanks for the feedback
-- Regards, Peo Sjoblom "liseladele" wrote in message ... Thank you so much for your help!! I finally got it to work!! Thanks again!! "Peo Sjoblom" wrote: Ok, first make a list with all sheet names, i.e. if your sheets are named Sheet1 Sheet2 Sheet3 etc make a list by typing all sheet names into that list, assume you type the list into K1:K15, now select K1:K15 with the mouse and type a name into the name box (name box is above column A's header) create this list in workbook1, name it for instance MySheets Assume that in workbook1 the lookup values start in A1 in Sheet1 in any cell in workbook1 sheet1 put =VLOOKUP(A1,INDIRECT("'[workbook2.xls]"&INDEX(MySheets,MATCH(TRUE,COUNTIF(IN DIRECT("'[workbook2.xls]"&MySheets&"'!A1:A50"),A1)0,0))&"'!A:B"),2,0) entered with ctrl + shift & enter will lookup what's in cell A1 in sheets 1 15 in workbook2, note that the workbooks need to be open -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... yes they are "Peo Sjoblom" wrote: Are all the tables in the sheets you want to lookup of the same size like Sheet2!A1:C100 Sheet3!A1:C100 ? -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... I need to be able to find a given text from workbook1 from any worksheet in workbook2 and be able to pull up the data from that worksheet(s) in workbook2 with the text into the worksheet in workbook2. Does that make since?? I don't really know the computer 'lingo'... "Peo Sjoblom" wrote: There are some fairly complex workarounds without using the add-in but you need to give us more information -- Regards, Peo Sjoblom (No private emails please) "liseladele" wrote in message ... Can you only use this as a function if you have downloaded something?? "Peo Sjoblom" wrote: I don't see any problems with that, if you would have asked that is what I would have suggested.. I always use dummy sheets when doing things like this (I even put one at the start as well) =VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0) The zero at the end tells it to look for an exact match while omitting it or using 1 (or TRUE for 1 and FALSE for 0) will look for the next largest value that is less than the lookup value, also the array must be sorted in ascending order so the first sheet must have the lowest value etc. By using 0 or FALSE it can be sorted in any order Good luck -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... I tried this and discovered a few problems that were self inflicted. I rectified them as follows: My VLOOKUP formula did not contain the ,0) at the end. I am not familiar with that! I believe the problem with the file structure arose where there was no worksheet M305. This made it create a link to look in the currect directory for a file that did not exist. To fix this where an M305 sheet is not needed, I intend to create a hidden sheet named "stop" as the last sheet and refer to it at the end within my formula. =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0) If you see any danger in theis approach, please let me know! Your help is so very much appreciated, Peo. Thank you very much! "Peo Sjoblom" wrote: This works for me =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0) or are you using this from another workbook =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0) replace test.xls with the name of the workbook The other workbook needs to be open or you'll get a REF error when you try to calculate it I am assuming here that the sheets are named M010:M305 -- Regards, Peo Sjoblom "Ray Stubblefield" wrote in message ... Good morning, Peo. I am having difficulty. Following your lead, I tried: =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2) It immediately changes to =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) Upon copying down my list of search criteria, I get the following: =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000 =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000 =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124 There are no worksheets is this particular workbook that the reference can search, so it is returning the previous result. I tried: =INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,TH REED('M010:M305'!$J$19)0), 2) It immediately changes to: =INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30 5'!$J$19)0),2) but, happily, it does give me the correct results. However, upon saving and closing, the cells contain: =INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2) This is not a valid reference to the worksheet, but only to the directory containing that worksheet. I do appreciate your help with this, Peo! Ray Stubbleefield "Peo Sjoblom" wrote: There is no built in lookuop that will work over multiple sheets, I would recommend using Laurent Longre's excellent Morefunc that can be downloaded here http://longre.free.fr/english/ descriptions here http://www.rhdatasolutions.com/morefunc/ In your case it would be =VLOOKUP(Lookup_value,THREED( etc Regards, Peo Sjoblom "Ray Stubblefield" wrote: I want to create a summary sheet that will lookup a particular cells value on multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19) based upon a cell next to it ($I$19) that will match the criteria on the summary sheet (e.g. w1, w2, w3). I have tried VLOOKAllSheets but when there are other similar workbooks open, it doesn't work right. Anyone? I appreciate your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the quickest method to insert & name multiple worksheets . | Excel Worksheet Functions | |||
How can I do a lookup to a specific cell on multiple worksheets? | Excel Worksheet Functions | |||
How to sum values in multiple worksheets | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
copy pivot table to multiple worksheets | Excel Worksheet Functions |