lookup of multiple sheets and getting the max value

[Nikhil]

Hi...have a problem....

I have a product available across different cities. This
data is available in different sheets. For eg.sheet A will
have product & stock in city A, sheet 2 will have product
& stock in city B...

In the summary sheet, i have a product list and i would
like to have the cityname where max stock of the
particular product is available. Also in the adjoining
column, i would like to have the stock figure that is
present in that particular city.

Plz note however that while in all sheets and the summary
sheet, the product is listed in columnA, the row no. for a
particular product would differ in different sheets.
e.g Product X might be in cell A4 in sheet1 but might be
in cell A7 in some other sheet or just might not be there
(if stock is zero) for some city.

One more thing to specify..is that in each sheet..for the
stock qty column, the column heading is the city name...

eg. cell A1 = Productname and cell B1 (which contains
stockqty) = CityA.

Hope this helps.

Plz help ASAP

Regards

Nikhil

 

[Harlan Grove]

and the lesson in all this...

keep you data together.

...

How nice. Users should always accomodate the most restrictive limitations of the
software rather than try to make it work the way they want/need (and many other
spreadsheets, those with true 3D functionality, do work).

your formulas are much simpler.

True, but if the OP needs the data to appear on multiple worksheets (and neither
of us can decide this - it's up to the OP), you've only shifted the nasty
formulas from this immediate calculation to returning the data to each of the
relevant worksheets. Not immediately obvious this is a net benefit.

Add-in or user-defined functions could make the formulas simple(r), though slow.

you can use filtering & sorting.

If there were such a need, FAR BETTER than putting all data into one worksheet
would be putting the data into a real RELATIONAL database. Then the OP could
take advantage of indexing on the fields that would represent city and stock
item to speed things up.

you can use lookups

Again, add-in or user-defined functions could handle this.

most important:
you can use pivottables to analyse/report your data across all
locations.

...

But pivot tables can be fed from external databases, so again, if the data is to
be consolidated, putting it in a single Excel worksheet isn't optimal.

You can't do this with Excel's built-in functionality. You'll need to use either
add-ins or user-defined formulas. If this is distributed for others to use, the
udf route may be more efficient, but I'll assume you're the only user. In which
case, download and install Laurent Longre's MOREFUNC.XLL add-in. You can
download it from http://longre.free.fr/english/ . It contains a function called
THREED which collapses 3D references into 2D arrays by stacking arrays of range
values vertically. This does require that you reference the same range on all
worksheets.

If the 3D reference were A:Z!A1:B99, then try something like the following in
cell B2 of the summary sheet for the product ID in cell A2.

=INDEX(THREED(A:Z!$A$1:$B$99),
INT((MATCH(MAX(IF(INDEX(THREED(A:Z!$A$1:$B$99),0,1)=A2,
INDEX(THREED(A:Z!$A$1:$B$99),0,2))),
INDEX(THREED(A:Z!$A$1:$B$99),0,2),0)-1)/99)*99+1,2)

where 99 is the number of rows in each worksheet in the 3D reference.