|
Conditional Question
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!!! |
Conditional Question
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 "H" wrote in message ... 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!!! |
Conditional Question
thanks Bernie
I think the highlighting is working but my formula to pull the year/qtr isn't. Here's what I typed =IF(MAX(B5:AK5)=1000000,INDEX($1:$1,1,MIN(IF(B5:A K5=1000000,COLUMN(B5:AK5),1000))),"Never above 1MM") "Bernie Deitrick" wrote: 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 "H" wrote in message ... 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!!! |
Conditional Question
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((ISN UMBER(B5:AK5))*(B5:AK5=1000000),COLUMN(B5:AK5),10 00))),"Never above 1MM") HTH, Bernie MS Excel MVP "H" wrote in message ... thanks Bernie I think the highlighting is working but my formula to pull the year/qtr isn't. Here's what I typed =IF(MAX(B5:AK5)=1000000,INDEX($1:$1,1,MIN(IF(B5:A K5=1000000,COLUMN(B5:AK5),1000))),"Never above 1MM") "Bernie Deitrick" wrote: 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 "H" wrote in message ... 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!!! |
Conditional Question
Thanks, here's what mine looks like when I do Ctrl-Shift Enter
=IF(MAX(B5:AK5)=1000000,INDEX(1:1,1,MIN(IF((ISNUM BER(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" wrote: 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((ISN UMBER(B5:AK5))*(B5:AK5=1000000),COLUMN(B5:AK5),10 00))),"Never above 1MM") HTH, Bernie MS Excel MVP "H" wrote in message ... thanks Bernie I think the highlighting is working but my formula to pull the year/qtr isn't. Here's what I typed =IF(MAX(B5:AK5)=1000000,INDEX($1:$1,1,MIN(IF(B5:A K5=1000000,COLUMN(B5:AK5),1000))),"Never above 1MM") "Bernie Deitrick" wrote: 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 "H" wrote in message ... 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!!! |
Conditional Question
Ah! You missed the $s on the INDEX(1:1 - that should be INDEX($1:$1
HTH, Bernie MS Excel MVP "H" wrote in message ... Thanks, here's what mine looks like when I do Ctrl-Shift Enter =IF(MAX(B5:AK5)=1000000,INDEX(1:1,1,MIN(IF((ISNUM BER(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" wrote: 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((ISN UMBER(B5:AK5))*(B5:AK5=1000000),COLUMN(B5:AK5),10 00))),"Never above 1MM") HTH, Bernie MS Excel MVP "H" wrote in message ... thanks Bernie I think the highlighting is working but my formula to pull the year/qtr isn't. Here's what I typed =IF(MAX(B5:AK5)=1000000,INDEX($1:$1,1,MIN(IF(B5:A K5=1000000,COLUMN(B5:AK5),1000))),"Never above 1MM") "Bernie Deitrick" wrote: 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 "H" wrote in message ... 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!!! |
Conditional Question
THANK YOU!!! YOU'RE BRILLIANT
"Bernie Deitrick" wrote: Ah! You missed the $s on the INDEX(1:1 - that should be INDEX($1:$1 HTH, Bernie MS Excel MVP "H" wrote in message ... Thanks, here's what mine looks like when I do Ctrl-Shift Enter =IF(MAX(B5:AK5)=1000000,INDEX(1:1,1,MIN(IF((ISNUM BER(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" wrote: 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((ISN UMBER(B5:AK5))*(B5:AK5=1000000),COLUMN(B5:AK5),10 00))),"Never above 1MM") HTH, Bernie MS Excel MVP "H" wrote in message ... thanks Bernie I think the highlighting is working but my formula to pull the year/qtr isn't. Here's what I typed =IF(MAX(B5:AK5)=1000000,INDEX($1:$1,1,MIN(IF(B5:A K5=1000000,COLUMN(B5:AK5),1000))),"Never above 1MM") "Bernie Deitrick" wrote: 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 "H" wrote in message ... 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!!! |
Conditional Question
THANK YOU!!!
You're welcome! YOU'RE BRILLIANT <Blush Bernie MS Excel MVP |
| All times are GMT +1. The time now is 09:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com