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.