| 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. |
| 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 | |||
Hybrid Mode
