Extensive V-Lookup

B

Byron720

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.
 
J

John C

What are you missing?
=VLOOKUP("your_criteria",THREED(Sheet1:Sheet4!A1:D100),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$1000),2,FALSE)
etc.
 
B

Byron720

I changed the formula to cover the length of the sheets but what I get is:
#VALUE!
 
J

John C

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?
 
B

Byron720

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
 
J

John C

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.)
 
J

John C

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)
 
B

Byron720

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.
 
J

John C

Glad it worked, and thanks for the feedback. The downside of INDIRECT is it
doesn't work on closed workbooks.
 
Top