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.