Any way to shorten this up?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin M - 06 Nov 2006 17:45 GMT

Hi all, TIA.. had a user send me a sheet today, they wish to add 5 more
values to look up. The problem is that the answer that this formula comes
from are set up horizontally, the target cells are vertical so no autofill.
The formula is such:

=IF(HLOOKUP('Price Performance Index'!B6,'Price Performance
Index'!C6:P18,3,FALSE)="N/A","FUTURE",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C6:P18,7,FALSE)=0,HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C6:P18,5,FALSE),IF(HLOOKUP('Price Performance Index'!B6,'Price
Performance Index'!C6:P18,5,FALSE)="N/A","N/A",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C6:P18,5,FALSE)>=(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C6:P18,7,FALSE)),"GREEN",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C6:P18,5,FALSE)<(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C6:P18,8,FALSE)),"RED","YELLOW")))))

There's got to be a way to shorten this up and repeat it across without
duplicating this nightmare?

Again, TIA.

Kevin M.

Reply to this Message

Harlan Grove - 06 Nov 2006 19:33 GMT

Kevin M wrote...

> . . . had a user send me a sheet today, they wish to add 5 more
>values to look up. The problem is that the answer that this formula comes
>from are set up horizontally, the target cells are vertical so no autofill.

Maybe no AUTOfill, but still possible to drag to fill. That is, maybe
they can't double click on the fill handle, but they could still drag
it right.

>The formula is such:
>
[quoted text clipped - 13 lines]
>There's got to be a way to shorten this up and repeat it across without
>duplicating this nightmare?

About the only thing you could do to shorten this would be replacing
the references to 'Price Performance Index'!B6 and 'Price Performance
Index'!C6:P18 with defined names, being careful to make range addresses
in those defined names RELATIVE if that's what's needed, and replace
the FALSE 4th arguments to HLOOKUP with equivalent 0.

Reply to this Message

Kevin M - 06 Nov 2006 19:50 GMT

Thanks as always Harlan.. I'll try setting 'Price Performance Index'!C6:P18
as static as the array data doesn't change from cell to cell jsut the index
number.

Kevin M.

> Kevin M wrote...
> > . . . had a user send me a sheet today, they wish to add 5 more
[quoted text clipped - 28 lines]
> in those defined names RELATIVE if that's what's needed, and replace
> the FALSE 4th arguments to HLOOKUP with equivalent 0.

Reply to this Message