Cell reference

[Bryan]

I have a spreadsheet with 7 workbooks in it. Each spreadsheet has a company
name and a price for a product.

On a master sheet I wrote a Min function that will choose the least cost of
each product and display it.

I would like to know which company had the least cost. In other words I
would like to know where the least cost price was pulled from.

thanks.

 

[Bob Phillips]

Something like

=MIN("Bayer Inc:Zeus plc'!A1)

where the two sheet names are the first and last, and A1 is the cell to test

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bryan]

That works to the extent of returning the lowest Cost. What I am interested
in is returning the company name of the sheet that has the lowest cost value

 

[Aladin Akyurek]

If I were you, bring them all those data together in a single worksheet
and apply the method I describe in:

http://tinyurl.com/22f83

Or investigate using pivot tables.

The point is that you might have multiple companies that ask the minimum
price for the same product.

 

[Harlan Grove]

Bryan wrote...

That works to the extent of returning the lowest Cost. What I am interested
in is returning the company name of the sheet that has the lowest cost value
on it.

....

To do that you need to create a list of worksheet names for all
worksheets to search. Call that list WSLST and use a formula like

=INDIRECT("'"&INDEX(WSLST,MATCH(MIN(N(INDIRECT("'"&WSLST&"'!A1"))),
N(INDIRECT("'"&WSLST&"'!A1")),0))&"'!B1")

Alternatively, download and install Laurent Longre's MOREFUNC.XLL
add-in, available from http://longre.free.fr/english/ , and try

=INDEX(THREED(Sheet1:Sheet4!B1),MATCH(MIN(THREED(Sheet1:Sheet4!A1)),
THREED(Sheet1:Sheet4!A1),0))

 

[Bryan]

U DA MAN!!!

Thanks it works perfectly. That was exactly what I was looking for.