Conditional Question

[H]

Hi -- is it possible to do conditional formatting that would highlight the
first time a customer hits $1,000,000 or above and then in another column
look for those highlighted and place the year and qtr that happened


I have a pivot table that looks like this

column A Column B Column C Column D etc....
(2000-1 thru 2010-1)
Customer 2006-1 2006-2 2006-3
Customer A $1,000,500 $2,000,000
Customer B $1,000,000 $3,000,000

So I would want to highlight
Customer A the amt in 2006-2 and then create a column in my
spreadsheet that states 2006-2

Customer B highlight the amt in 2006-1 and in another column state 2006-1

Can anyone help?
Thank you!!!

 

[Bernie Deitrick]

H,

Select your cells, then use the CF with the formula

=AND(B2>=1000000,MAX($A2:A2)<1000000)

where the Activecell is B2 at the time that you apply the formula.

To extract the year/quarter, use the array formula (enter using Ctrl-Shift-Enter)

=IF(MAX(B2:Z2)>=1000000,INDEX($1:$1,1,MIN(IF(B2:Z2>=1000000,COLUMN(B2:Z2),1000))),"Never above 1MM")

Change the Zs to the column letter of your last column. This assumes that you 2006-1 etc values are
in row 1.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

H,

That works fine for me - did you array enter it (Ctrl-Shift-Enter instead of just Enter)?

If you have spaces or other text in B5:AK5, then you need to array enter a formula like this:

=IF(MAX(B5:AK5)>=1000000,INDEX($1:$1,1,MIN(IF((ISNUMBER(B5:AK5))*(B5:AK5>=1000000),COLUMN(B5:AK5),1000))),"Never
above 1MM")

HTH,
Bernie
MS Excel MVP

 

[H]

Thanks, here's what mine looks like when I do Ctrl-Shift Enter
=IF(MAX(B5:AK5)>=1000000,INDEX(1:1,1,MIN(IF((ISNUMBER(B5:AK5))*(B5:AK5>=1000000),COLUMN(B5:AK5),1000))),"Never ")

but it has brackets around the outside

Out of 70 rows it's only pulling one in with the year/qtr

 

[Bernie Deitrick]

Ah! You missed the $s on the INDEX(1:1 - that should be INDEX($1:$1

HTH,
Bernie
MS Excel MVP

 

[H]

THANK YOU!!! YOU'RE BRILLIANT

Ah! You missed the $s on the INDEX(1:1 - that should be INDEX($1:$1

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

THANK YOU!!!

You're welcome!

YOU'RE BRILLIANT

<Blush>

Bernie
MS Excel MVP