Find each lowest value and return sheet name

**Astaroths** · 02-01-2010, 11:36 AM

I have a series of worksheets each containing a 10x10 table containing prices. Each sheet represents a different supplier.

What I need to do is now create a summary sheet and have the same 10x10 table but rather than listing prices list the name of the company (ie the sheet name) that had the lowest value (ie compare the cell B3 across all the sheets and return the name of the sheet with the lowest value in B3)

I am struggling to do this as the few similar questions have had suggestions of merging the data to a single page but having 10x10 tables the solutions previously suggested wouldnt work

Many thanks

**sweep** · 02-01-2010, 11:44 AM

Hi,

To return the lowest value in the same cell across several sheets, use something like this:

=MIN(Sheet1:Sheet10!B3)

To lookup across sheets, you will need an user-defined formula, or a complex workaround - there are several posted hereaouts, try a search for "lookup across multiple sheets" or "index and match across multiple sheets"

**DonkeyOte** · 02-01-2010, 11:52 AM

There are a few approach as outlined...

One non-VBA way would be to replicate the 10x10 on each sheet, if we assume say your 10x10 is A1:J10 on each sheet, eg:

Please Login or Register  to view this content.

(above replicated for each of the 10 sheets - can be done simultaneously by grouping)

Once the above is done - then on your Summary sheet:

Please Login or Register  to view this content.

above assumes sheet names do not contain spaces and will concatenate multiple sheet names should have 1+ instance of any given MIN value
(if they do the above would need to be adjusted)

If you need more assistance - post a sample

**enots82** · 04-30-2014, 09:57 AM

Hi

Could someone help me with this, I currently have 11 worksheets filled with pricing information in the same cells on each worksheet:
B7:M21

I use =MIN(Sheet1!B7,Sheet2!B7,Sheet3!B7,Sheet4!B7,Sheet5!B7,Sheet6!B7,Sheet7!B7,Sheet8!B7,Sheet9!B7,Sheet10!B7,Sheet11!B7) to display the lowest price in the table from all 11 suppliers, however I was wondering with a simple addition of code could this display the price + Sheet? (ie sheet name) from which the price came from? i dont mind if i have to add an additional row to insert the supplier name for each best price

any help with this would be greatly appreciated

Thanks

phil