Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Any way to shorten this up?

1 view
Skip to first unread message

Kevin M

unread,
Nov 6, 2006, 12:45:02 PM11/6/06
to
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.

Harlan Grove

unread,
Nov 6, 2006, 2:33:50 PM11/6/06
to
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:
>
>=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?

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.

Kevin M

unread,
Nov 6, 2006, 2:50:01 PM11/6/06
to
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.

0 new messages