Cell reference
 

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.

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" wrote in message
...
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.

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.


"Bob Phillips" wrote:

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" wrote in message
...
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.

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.

Bryan wrote:
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.

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(S heet1:Sheet4!A1)),
THREED(Sheet1:Sheet4!A1),0))

U DA MAN!!!

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

"Aladin Akyurek" wrote:

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.

Bryan wrote:
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.