| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extensive V-Lookup
I have a report with this information:
Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
From a previous post by Teethless Mama
Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ....then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
I'm almost there but I can't figure out the entire formula.
"John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
What are you missing?
=VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
I changed the formula to cover the length of the sheets but what I get is:
#VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
Where is the #VALUE error coming from. If you use the formula auditing
toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
This is my formula:
=VLOOKUP(B6,THREED(ICost1:ICost4!A1:E65535),2,0) When I created it, on the table array field I typed: THREED(ICost1:ICost4!A1:E65535) and next to it #VALUE! appeared "John C" wrote: Where is the #VALUE error coming from. If you use the formula auditing toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
Did you read the help on THREED?
--This argument can refer to an external range ('[File.xls]Sheet1:Sheet8'!A1:C30), but the source file must be open. Its size is limited to 65536 items (therefore, the 3D range can't contain more than 65536 cells). --You are encompassing 4 sheet tabs, 65535 rows, and 5 columns, for a total over 1,000,000 cells, so maybe this won't work. NOTE: The MOREFUNC add-in is still quite useful with all the additional functions. But my question is, what is the separation point on the parts. Are some parts on ICost1 and some on ICost2 for different reasons? Or are the page separations on different pages just because of the sheer number of parts. And in that case, are they in any particular order (alphabetical, numerical, etc.) -- ** John C ** "Byron720" wrote: This is my formula: =VLOOKUP(B6,THREED(ICost1:ICost4!A1:E65535),2,0) When I created it, on the table array field I typed: THREED(ICost1:ICost4!A1:E65535) and next to it #VALUE! appeared "John C" wrote: Where is the #VALUE error coming from. If you use the formula auditing toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
If there is no rhyme or reason as to how the parts are separated (i.e.: just
added as they come in, then this formula will work, albeit a little clunky: =VLOOKUP(A19,INDIRECT("ICost"&COUNTIF(ICost1!$A$1: $A$65535,A2)*1+COUNTIF(ICost2!$A$1:$A$65535,A2)*2+ COUNTIF(ICost3!$A$1:$A$65535,A2)*3+COUNTIF(ICost4! $A$1:$A$65535,A2)*4&"!$A$1:$E$65535"),2,0) -- ** John C ** "Byron720" wrote: This is my formula: =VLOOKUP(B6,THREED(ICost1:ICost4!A1:E65535),2,0) When I created it, on the table array field I typed: THREED(ICost1:ICost4!A1:E65535) and next to it #VALUE! appeared "John C" wrote: Where is the #VALUE error coming from. If you use the formula auditing toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
Sorry for the late response. This long and clunky formula worked perfectly. I
have to learn a little more about INDIRECT. A very useful function. "John C" wrote: If there is no rhyme or reason as to how the parts are separated (i.e.: just added as they come in, then this formula will work, albeit a little clunky: =VLOOKUP(A19,INDIRECT("ICost"&COUNTIF(ICost1!$A$1: $A$65535,A2)*1+COUNTIF(ICost2!$A$1:$A$65535,A2)*2+ COUNTIF(ICost3!$A$1:$A$65535,A2)*3+COUNTIF(ICost4! $A$1:$A$65535,A2)*4&"!$A$1:$E$65535"),2,0) -- ** John C ** "Byron720" wrote: This is my formula: =VLOOKUP(B6,THREED(ICost1:ICost4!A1:E65535),2,0) When I created it, on the table array field I typed: THREED(ICost1:ICost4!A1:E65535) and next to it #VALUE! appeared "John C" wrote: Where is the #VALUE error coming from. If you use the formula auditing toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extensive V-Lookup
Glad it worked, and thanks for the feedback. The downside of INDIRECT is it
doesn't work on closed workbooks. -- ** John C ** "Byron720" wrote: Sorry for the late response. This long and clunky formula worked perfectly. I have to learn a little more about INDIRECT. A very useful function. "John C" wrote: If there is no rhyme or reason as to how the parts are separated (i.e.: just added as they come in, then this formula will work, albeit a little clunky: =VLOOKUP(A19,INDIRECT("ICost"&COUNTIF(ICost1!$A$1: $A$65535,A2)*1+COUNTIF(ICost2!$A$1:$A$65535,A2)*2+ COUNTIF(ICost3!$A$1:$A$65535,A2)*3+COUNTIF(ICost4! $A$1:$A$65535,A2)*4&"!$A$1:$E$65535"),2,0) -- ** John C ** "Byron720" wrote: This is my formula: =VLOOKUP(B6,THREED(ICost1:ICost4!A1:E65535),2,0) When I created it, on the table array field I typed: THREED(ICost1:ICost4!A1:E65535) and next to it #VALUE! appeared "John C" wrote: Where is the #VALUE error coming from. If you use the formula auditing toolbar, there is a choice of evaluate formula, it will take step by step through the formula and will tell you exactly when the error will occur. What is your actual formula? -- ** John C ** "Byron720" wrote: I changed the formula to cover the length of the sheets but what I get is: #VALUE! "John C" wrote: What are you missing? =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) your criteria is your part number that you are looking up your table is your first sheet name:last sheet name that has the info you are looking for. I noticed, however, I only put a D when you have 5 columns of DATA, so it should be an E, then the 2 in the formula above would be part description, for New Buy you would have the same formula, change it to a 3, a 4 for RV, and 5 for Net Repair. Obvioulsy, if your data on your worksheets varies in length, you want to make sure that the last row will cover the sheet with the most parts.... =VLOOKUP(criteria,THREED(Sheet1:Sheet4!$A$2:$E$100 0),2,FALSE) etc. -- ** John C ** "Byron720" wrote: I'm almost there but I can't figure out the entire formula. "John C" wrote: From a previous post by Teethless Mama Download and install the free add-in Morefunc.xll from: http://www.download.com/Morefunc/300...-10423159.html The MOREFUNC site has been hacked. http://xcell05.free.fr/ ...then use this formula =VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D 100),2,0) etc... -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Byron720" wrote: I have a report with this information: Part Part Description New Buy RV Net Repair The report is 4 worksheet long. I need a formula that looks thru the 4 worksheets and finds the information for any part # I type. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make Forecasts - How extensive is it? | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Extensive Workbook Creation | New Users to Excel | |||
| How can I write for extensive a number (Excel) ? | Excel Worksheet Functions | |||
Linear Mode
