Here's a poser. I have a summary sheet which compiles data from 10 other
worksheets within the same workbook. The summary sheet looks for values of
MAX, MIN and AVERAGE across the 10 worksheets, and retrieve the values
accordingly. What i need, however, is not the value but rather the text in
cell AA1 that corresponds to the values retrieved. Here's an example:

Example, in the summary page:
MAX(Start:End!O2) will retrieve and display the maximum value across the
worksheets in cell O2 of all ten w'sheets. I will need a formula which will
then retrieve the text stored in cell AA1 of the worksheet that has the
maximum value, and display it on the summary page (beside the cell which
shows the result of the maximum value).

Is there any way to do this without macros or UDF?

Just to add, this was the failed formula that i tried on the summary sheet:
(A2 = Max(Start:End!O2)

=INDEX(Start:End!O1:AA4),MATCH(A2,Start:End!O2,0), 13)

"andrew" wrote:

Here's a poser. I have a summary sheet which compiles data from 10 other
worksheets within the same workbook. The summary sheet looks for values of
MAX, MIN and AVERAGE across the 10 worksheets, and retrieve the values
accordingly. What i need, however, is not the value but rather the text in
cell AA1 that corresponds to the values retrieved. Here's an example:

Example, in the summary page:
MAX(Start:End!O2) will retrieve and display the maximum value across the
worksheets in cell O2 of all ten w'sheets. I will need a formula which will
then retrieve the text stored in cell AA1 of the worksheet that has the
maximum value, and display it on the summary page (beside the cell which
shows the result of the maximum value).

Is there any way to do this without macros or UDF?

Without using a macro to 1) Find the calculated value, 2) return the
associated label, there's no way of doing this across multiple sheets

"andrew" wrote:

Just to add, this was the failed formula that i tried on the summary sheet:
(A2 = Max(Start:End!O2)

=INDEX(Start:End!O1:AA4),MATCH(A2,Start:End!O2,0), 13)

"andrew" wrote:

Here's a poser. I have a summary sheet which compiles data from 10 other
worksheets within the same workbook. The summary sheet looks for values of
MAX, MIN and AVERAGE across the 10 worksheets, and retrieve the values
accordingly. What i need, however, is not the value but rather the text in
cell AA1 that corresponds to the values retrieved. Here's an example:

Example, in the summary page:
MAX(Start:End!O2) will retrieve and display the maximum value across the
worksheets in cell O2 of all ten w'sheets. I will need a formula which will
then retrieve the text stored in cell AA1 of the worksheet that has the
maximum value, and display it on the summary page (beside the cell which
shows the result of the maximum value).

Is there any way to do this without macros or UDF?

Ok. How do i get this working with macro? (i posted this query before but
unfortunately the macro provided didn't completely work).

"Duke Carey" wrote:

Without using a macro to 1) Find the calculated value, 2) return the
associated label, there's no way of doing this across multiple sheets

"andrew" wrote:

Just to add, this was the failed formula that i tried on the summary sheet:
(A2 = Max(Start:End!O2)

=INDEX(Start:End!O1:AA4),MATCH(A2,Start:End!O2,0), 13)

"andrew" wrote:

Here's a poser. I have a summary sheet which compiles data from 10 other
worksheets within the same workbook. The summary sheet looks for values of
MAX, MIN and AVERAGE across the 10 worksheets, and retrieve the values
accordingly. What i need, however, is not the value but rather the text in
cell AA1 that corresponds to the values retrieved. Here's an example:

Example, in the summary page:
MAX(Start:End!O2) will retrieve and display the maximum value across the
worksheets in cell O2 of all ten w'sheets. I will need a formula which will
then retrieve the text stored in cell AA1 of the worksheet that has the
maximum value, and display it on the summary page (beside the cell which
shows the result of the maximum value).

Is there any way to do this without macros or UDF?