conditional formating: ifs and highlighting rows

my spreadsheet documents error incidents, with each row showing the date the 
incident was discovered (column A) and the date it was resolved (column I).  
it also calculates networkdays (column J) -- unless column I=0 -- and 
references an array of holiday dates on another sheet.

i would like to create a conditional format that will identify rows with an 
incident, but no resolution date, then highlight the row and possibly even 
show "unresolved" in column J cell of that row.

Thanks!
0
Utf
12/22/2009 6:05:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
959 Views

Similar Articles

[PageSpeed] 23

Well, you have to decide which way you want to go with this.

If you leave column J blank then you can base the CF on column J being 
blank.

If you want column J to return "unresolved" then you can base the CF on 
column J being "unresolved".

Either way, it's not difficult.

-- 
Biff
Microsoft Excel MVP


"Anna S" <Anna S@discussions.microsoft.com> wrote in message 
news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> my spreadsheet documents error incidents, with each row showing the date 
> the
> incident was discovered (column A) and the date it was resolved (column 
> I).
> it also calculates networkdays (column J) -- unless column I=0 -- and
> references an array of holiday dates on another sheet.
>
> i would like to create a conditional format that will identify rows with 
> an
> incident, but no resolution date, then highlight the row and possibly even
> show "unresolved" in column J cell of that row.
>
> Thanks! 


0
T
12/22/2009 10:54:20 PM
The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
so J shows blank on blank rows as well as unresolved rows.  So I think this 
means I need a formula that checks column A for a date (cuz if it's blank, 
then the row stays the same), and then checks column I for a date (and if 
this is blank, then the row should be highlighted)?  or is there an easier 
way to do this?

i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and 
=if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while 
columns A-J are highlighted), with a fill color.  but they did nothing.

"T. Valko" wrote:

> Well, you have to decide which way you want to go with this.
> 
> If you leave column J blank then you can base the CF on column J being 
> blank.
> 
> If you want column J to return "unresolved" then you can base the CF on 
> column J being "unresolved".
> 
> Either way, it's not difficult.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Anna S" <Anna S@discussions.microsoft.com> wrote in message 
> news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> > my spreadsheet documents error incidents, with each row showing the date 
> > the
> > incident was discovered (column A) and the date it was resolved (column 
> > I).
> > it also calculates networkdays (column J) -- unless column I=0 -- and
> > references an array of holiday dates on another sheet.
> >
> > i would like to create a conditional format that will identify rows with 
> > an
> > incident, but no resolution date, then highlight the row and possibly even
> > show "unresolved" in column J cell of that row.
> >
> > Thanks! 
> 
> 
> .
> 
0
Utf
12/23/2009 2:20:01 PM
ok i played around a bit and got this
=AND($A1>0,$J1="")
to work for highlighting.  didn't realize it needed to reference the first 
row to work properly :-P

any thoughts on getting "unresolved" in the J-cell?

"Anna S" wrote:

> The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
> so J shows blank on blank rows as well as unresolved rows.  So I think this 
> means I need a formula that checks column A for a date (cuz if it's blank, 
> then the row stays the same), and then checks column I for a date (and if 
> this is blank, then the row should be highlighted)?  or is there an easier 
> way to do this?
> 
> i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and 
> =if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while 
> columns A-J are highlighted), with a fill color.  but they did nothing.
> 
> "T. Valko" wrote:
> 
> > Well, you have to decide which way you want to go with this.
> > 
> > If you leave column J blank then you can base the CF on column J being 
> > blank.
> > 
> > If you want column J to return "unresolved" then you can base the CF on 
> > column J being "unresolved".
> > 
> > Either way, it's not difficult.
> > 
> > -- 
> > Biff
> > Microsoft Excel MVP
> > 
> > 
> > "Anna S" <Anna S@discussions.microsoft.com> wrote in message 
> > news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> > > my spreadsheet documents error incidents, with each row showing the date 
> > > the
> > > incident was discovered (column A) and the date it was resolved (column 
> > > I).
> > > it also calculates networkdays (column J) -- unless column I=0 -- and
> > > references an array of holiday dates on another sheet.
> > >
> > > i would like to create a conditional format that will identify rows with 
> > > an
> > > incident, but no resolution date, then highlight the row and possibly even
> > > show "unresolved" in column J cell of that row.
> > >
> > > Thanks! 
> > 
> > 
> > .
> > 
0
Utf
12/23/2009 5:53:01 PM
>I need a formula that checks column A for a date
>(cuz if it's blank, then the row stays the same),
>and then checks column I for a date (and if this is
>blank, then the row should be highlighted)?

Yes

Try this as the formula CF formula:

=AND(COUNT($A2),$I2="")

-- 
Biff
Microsoft Excel MVP


"Anna S" <AnnaS@discussions.microsoft.com> wrote in message 
news:2816A478-E208-4BBB-8450-E74B73E94E6B@microsoft.com...
> The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
> so J shows blank on blank rows as well as unresolved rows.  So I think 
> this
> means I need a formula that checks column A for a date (cuz if it's blank,
> then the row stays the same), and then checks column I for a date (and if
> this is blank, then the row should be highlighted)?  or is there an easier
> way to do this?
>
> i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and
> =if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while
> columns A-J are highlighted), with a fill color.  but they did nothing.
>
> "T. Valko" wrote:
>
>> Well, you have to decide which way you want to go with this.
>>
>> If you leave column J blank then you can base the CF on column J being
>> blank.
>>
>> If you want column J to return "unresolved" then you can base the CF on
>> column J being "unresolved".
>>
>> Either way, it's not difficult.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Anna S" <Anna S@discussions.microsoft.com> wrote in message
>> news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
>> > my spreadsheet documents error incidents, with each row showing the 
>> > date
>> > the
>> > incident was discovered (column A) and the date it was resolved (column
>> > I).
>> > it also calculates networkdays (column J) -- unless column I=0 -- and
>> > references an array of holiday dates on another sheet.
>> >
>> > i would like to create a conditional format that will identify rows 
>> > with
>> > an
>> > incident, but no resolution date, then highlight the row and possibly 
>> > even
>> > show "unresolved" in column J cell of that row.
>> >
>> > Thanks!
>>
>>
>> .
>> 


0
T
12/23/2009 6:01:13 PM
That worked too when i substituted the row reference 1's for the 2's.

i still can't figure out how to get the j-cell to show "unresolved" but 
that's not too big of a deal.  just want to know for curiosity-sake

Thanks!

"T. Valko" wrote:

> >I need a formula that checks column A for a date
> >(cuz if it's blank, then the row stays the same),
> >and then checks column I for a date (and if this is
> >blank, then the row should be highlighted)?
> 
> Yes
> 
> Try this as the formula CF formula:
> 
> =AND(COUNT($A2),$I2="")
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Anna S" <AnnaS@discussions.microsoft.com> wrote in message 
> news:2816A478-E208-4BBB-8450-E74B73E94E6B@microsoft.com...
> > The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
> > so J shows blank on blank rows as well as unresolved rows.  So I think 
> > this
> > means I need a formula that checks column A for a date (cuz if it's blank,
> > then the row stays the same), and then checks column I for a date (and if
> > this is blank, then the row should be highlighted)?  or is there an easier
> > way to do this?
> >
> > i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and
> > =if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while
> > columns A-J are highlighted), with a fill color.  but they did nothing.
> >
> > "T. Valko" wrote:
> >
> >> Well, you have to decide which way you want to go with this.
> >>
> >> If you leave column J blank then you can base the CF on column J being
> >> blank.
> >>
> >> If you want column J to return "unresolved" then you can base the CF on
> >> column J being "unresolved".
> >>
> >> Either way, it's not difficult.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Anna S" <Anna S@discussions.microsoft.com> wrote in message
> >> news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> >> > my spreadsheet documents error incidents, with each row showing the 
> >> > date
> >> > the
> >> > incident was discovered (column A) and the date it was resolved (column
> >> > I).
> >> > it also calculates networkdays (column J) -- unless column I=0 -- and
> >> > references an array of holiday dates on another sheet.
> >> >
> >> > i would like to create a conditional format that will identify rows 
> >> > with
> >> > an
> >> > incident, but no resolution date, then highlight the row and possibly 
> >> > even
> >> > show "unresolved" in column J cell of that row.
> >> >
> >> > Thanks!
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
12/23/2009 7:20:01 PM
nm.  got it.  changed the j formula to
=IF($I2=0,IF($A2=0,"","unresolved"),NETWORKDAYS($A2,$I2,holidays))

"Anna S" wrote:

> That worked too when i substituted the row reference 1's for the 2's.
> 
> i still can't figure out how to get the j-cell to show "unresolved" but 
> that's not too big of a deal.  just want to know for curiosity-sake
> 
> Thanks!
> 
> "T. Valko" wrote:
> 
> > >I need a formula that checks column A for a date
> > >(cuz if it's blank, then the row stays the same),
> > >and then checks column I for a date (and if this is
> > >blank, then the row should be highlighted)?
> > 
> > Yes
> > 
> > Try this as the formula CF formula:
> > 
> > =AND(COUNT($A2),$I2="")
> > 
> > -- 
> > Biff
> > Microsoft Excel MVP
> > 
> > 
> > "Anna S" <AnnaS@discussions.microsoft.com> wrote in message 
> > news:2816A478-E208-4BBB-8450-E74B73E94E6B@microsoft.com...
> > > The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
> > > so J shows blank on blank rows as well as unresolved rows.  So I think 
> > > this
> > > means I need a formula that checks column A for a date (cuz if it's blank,
> > > then the row stays the same), and then checks column I for a date (and if
> > > this is blank, then the row should be highlighted)?  or is there an easier
> > > way to do this?
> > >
> > > i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) and
> > > =if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting (while
> > > columns A-J are highlighted), with a fill color.  but they did nothing.
> > >
> > > "T. Valko" wrote:
> > >
> > >> Well, you have to decide which way you want to go with this.
> > >>
> > >> If you leave column J blank then you can base the CF on column J being
> > >> blank.
> > >>
> > >> If you want column J to return "unresolved" then you can base the CF on
> > >> column J being "unresolved".
> > >>
> > >> Either way, it's not difficult.
> > >>
> > >> -- 
> > >> Biff
> > >> Microsoft Excel MVP
> > >>
> > >>
> > >> "Anna S" <Anna S@discussions.microsoft.com> wrote in message
> > >> news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> > >> > my spreadsheet documents error incidents, with each row showing the 
> > >> > date
> > >> > the
> > >> > incident was discovered (column A) and the date it was resolved (column
> > >> > I).
> > >> > it also calculates networkdays (column J) -- unless column I=0 -- and
> > >> > references an array of holiday dates on another sheet.
> > >> >
> > >> > i would like to create a conditional format that will identify rows 
> > >> > with
> > >> > an
> > >> > incident, but no resolution date, then highlight the row and possibly 
> > >> > even
> > >> > show "unresolved" in column J cell of that row.
> > >> >
> > >> > Thanks!
> > >>
> > >>
> > >> .
> > >> 
> > 
> > 
> > .
> > 
0
Utf
12/23/2009 7:31:02 PM
Try it like this...

=IF(COUNT(A2,I2)=2,NETWORKDAYS($A2,$I2,holidays),IF(AND(COUNT(A2),I2=""),"unresolved",""))

Here's the beakdown:

If A2 and I2 both contain dates execute NETWOKDAYS

If A2 contains a date but I2 is blank/empty return unresolved.

Any other condition return blank

-- 
Biff
Microsoft Excel MVP


"Anna S" <AnnaS@discussions.microsoft.com> wrote in message 
news:916B106E-803D-4611-9C5E-B6F0F6F61093@microsoft.com...
> nm.  got it.  changed the j formula to
> =IF($I2=0,IF($A2=0,"","unresolved"),NETWORKDAYS($A2,$I2,holidays))
>
> "Anna S" wrote:
>
>> That worked too when i substituted the row reference 1's for the 2's.
>>
>> i still can't figure out how to get the j-cell to show "unresolved" but
>> that's not too big of a deal.  just want to know for curiosity-sake
>>
>> Thanks!
>>
>> "T. Valko" wrote:
>>
>> > >I need a formula that checks column A for a date
>> > >(cuz if it's blank, then the row stays the same),
>> > >and then checks column I for a date (and if this is
>> > >blank, then the row should be highlighted)?
>> >
>> > Yes
>> >
>> > Try this as the formula CF formula:
>> >
>> > =AND(COUNT($A2),$I2="")
>> >
>> > -- 
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "Anna S" <AnnaS@discussions.microsoft.com> wrote in message
>> > news:2816A478-E208-4BBB-8450-E74B73E94E6B@microsoft.com...
>> > > The formula in J is: =IF(I2=0,"",NETWORKDAYS(A2,I2,holidays))
>> > > so J shows blank on blank rows as well as unresolved rows.  So I 
>> > > think
>> > > this
>> > > means I need a formula that checks column A for a date (cuz if it's 
>> > > blank,
>> > > then the row stays the same), and then checks column I for a date 
>> > > (and if
>> > > this is blank, then the row should be highlighted)?  or is there an 
>> > > easier
>> > > way to do this?
>> > >
>> > > i tried using the formulas =if($a2<>"",if($i2="",$j2="unresolved")) 
>> > > and
>> > > =if($a2>0,if($i2=0,$j2="unresolved")) in conditional formatting 
>> > > (while
>> > > columns A-J are highlighted), with a fill color.  but they did 
>> > > nothing.
>> > >
>> > > "T. Valko" wrote:
>> > >
>> > >> Well, you have to decide which way you want to go with this.
>> > >>
>> > >> If you leave column J blank then you can base the CF on column J 
>> > >> being
>> > >> blank.
>> > >>
>> > >> If you want column J to return "unresolved" then you can base the CF 
>> > >> on
>> > >> column J being "unresolved".
>> > >>
>> > >> Either way, it's not difficult.
>> > >>
>> > >> -- 
>> > >> Biff
>> > >> Microsoft Excel MVP
>> > >>
>> > >>
>> > >> "Anna S" <Anna S@discussions.microsoft.com> wrote in message
>> > >> news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
>> > >> > my spreadsheet documents error incidents, with each row showing 
>> > >> > the
>> > >> > date
>> > >> > the
>> > >> > incident was discovered (column A) and the date it was resolved 
>> > >> > (column
>> > >> > I).
>> > >> > it also calculates networkdays (column J) -- unless column I=0 --  
>> > >> > and
>> > >> > references an array of holiday dates on another sheet.
>> > >> >
>> > >> > i would like to create a conditional format that will identify 
>> > >> > rows
>> > >> > with
>> > >> > an
>> > >> > incident, but no resolution date, then highlight the row and 
>> > >> > possibly
>> > >> > even
>> > >> > show "unresolved" in column J cell of that row.
>> > >> >
>> > >> > Thanks!
>> > >>
>> > >>
>> > >> .
>> > >>
>> >
>> >
>> > .
>> > 


0
T
12/23/2009 9:17:58 PM
On Dec 23, 2:20=A0pm, Anna S <An...@discussions.microsoft.com> wrote:
> That worked too when i substituted the row reference 1's for the 2's.
>
> i still can't figure out how to get the j-cell to show "unresolved" but
> that's not too big of a deal. =A0just want to know for curiosity-sake
>
> Thanks!
>
>
>
> "T. Valko" wrote:
> > >I need a formula that checks column A for a date
> > >(cuz if it's blank, then the row stays the same),
> > >and then checks column I for a date (and if this is
> > >blank, then the row should be highlighted)?
>
> > Yes
>
> > Try this as the formula CF formula:
>
> > =3DAND(COUNT($A2),$I2=3D"")
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > "Anna S" <An...@discussions.microsoft.com> wrote in message
> >news:2816A478-E208-4BBB-8450-E74B73E94E6B@microsoft.com...
> > > The formula in J is: =3DIF(I2=3D0,"",NETWORKDAYS(A2,I2,holidays))
> > > so J shows blank on blank rows as well as unresolved rows. =A0So I th=
ink
> > > this
> > > means I need a formula that checks column A for a date (cuz if it's b=
lank,
> > > then the row stays the same), and then checks column I for a date (an=
d if
> > > this is blank, then the row should be highlighted)? =A0or is there an=
 easier
> > > way to do this?
>
> > > i tried using the formulas =3Dif($a2<>"",if($i2=3D"",$j2=3D"unresolve=
d")) and
> > > =3Dif($a2>0,if($i2=3D0,$j2=3D"unresolved")) in conditional formatting=
 (while
> > > columns A-J are highlighted), with a fill color. =A0but they did noth=
ing.
>
> > > "T. Valko" wrote:
>
> > >> Well, you have to decide which way you want to go with this.
>
> > >> If you leave column J blank then you can base the CF on column J bei=
ng
> > >> blank.
>
> > >> If you want column J to return "unresolved" then you can base the CF=
 on
> > >> column J being "unresolved".
>
> > >> Either way, it's not difficult.
>
> > >> --
> > >> Biff
> > >> Microsoft Excel MVP
>
> > >> "Anna S" <Anna S...@discussions.microsoft.com> wrote in message
> > >>news:D001542D-E8FA-4CCE-B67D-B58C176226D8@microsoft.com...
> > >> > my spreadsheet documents error incidents, with each row showing th=
e
> > >> > date
> > >> > the
> > >> > incident was discovered (column A) and the date it was resolved (c=
olumn
> > >> > I).
> > >> > it also calculates networkdays (column J) -- unless column I=3D0 -=
- and
> > >> > references an array of holiday dates on another sheet.
>
> > >> > i would like to create a conditional format that will identify row=
s
> > >> > with
> > >> > an
> > >> > incident, but no resolution date, then highlight the row and possi=
bly
> > >> > even
> > >> > show "unresolved" in column J cell of that row.
>
> > >> > Thanks!
>
> > >> .
>
> > .- Hide quoted text -
>
> - Show quoted text -

If for some reason you don't actually want the word unresolved in
column J, maybe to facilitate data validation, you can use a custom
number format such as

mm-dd-yy;-0;u\n\r\e\solv\e\d

to show a date if a date is entered, the number if a negative number
is entered or the word unresolved if it is zero.

Ken

0
Ken
12/23/2009 10:23:27 PM
Reply:

Similar Artilces:

Change default font format in Excel
I have an existing workbook with cell text entries in regular black font. I want to make a number of additional entries in various cells in this workbook, and I want all my text entries to be a different font format (bold, red). Is there a way to do this automatically without highlighting each entry I make and manually changing the cell format? Thanks. Michael, Copy the code below, right-click on your sheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Cou...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Every cell is highlighted
This is a new one for me. Afer opening up Excel '98 and go to a cell, every cell is highlighted when I move the mouse. I can not put any information in any cell. The only way to quit Excel is control, alt, delete and then it gives me "can not quit excel". It does though after a while. Wherever I move the mouse, the cells are highlighted. What gives? Thanks f8 key -- Don Guillett SalesAid Software donaldb@281.com "5hulses" <5hulses@discussions.microsoft.com> wrote in message news:3B8F2E7E-8957-4B26-B64B-AEC88B6099BE@microsoft.com... > This is a new...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

Highlight only the second instance of a duplicate
Hello, I am trying to apply conditional formatting to a list where only the second and subsquent duplicates should be highlighted. I've tried =COUNTIF($A$2:$A2,A2)>1 and several other variations but they all result in highlighting the first result, which is the opposite of what I want. Any suggestions? Thanks! Tiffany I think you either posted the wrong formula or you did something wrong in your worksheet. Your conditional formatting formula worked fine for me when I tested it. One more question... Did you select the range and apply the formula to all the cells in that selection...

Formating
Hi, In a Column 'A' sales Commission is calculated and resulta is as 250, 200, 300, 330. I wanted to format a cell with a Blinking colours where value is 100 to 250, 250 to 300, 300 to 350 and 350 & above. please help to format my sheet ... thanks Ismail, Getting your cells to "blink" is not in the standard Excel formats What you want would require some extensive programming, certainly, ou of my capabilities. However, there are some great things you can do with Excel' CONDITIONAL FORMATTING. Let's say in column A you have numbers that are the result of formu...

Highlighted Cell
Hi all If I highlight A1 is there a way to have the cell fill colour yellow, So I can see where I am better, when I move to say A8 etc. A1 go's back to its normal colour and the new cell is yellow. Thanks in advance Dave See if this helps: http://www.cpearson.com/excel/RowLiner.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" <tuco@tuco.karoo.co.uk> wrote in messag...

Supress col/row increment with copy
Question with (hopefully) an easy answer.... When you copy a cell that uses a formula referring to another cell, for simple illustration say "=C4" it will increment based on where you paste it. So, it will increment up to "=C5" if you paste it in the next cell, or "=D4" if you paste below. How can I suppress that, so that when I paste the new cell receives "=C4" as well. (I know I can simply copy the text and paste that in, but I want to copy a whole row of formulas to be the same. I thought Paste>Special>Formulas would work, but it seems to ...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Opening and formating a CSV file?
Could someone remind me how to handle CSV files please? I had assumed the Text Import Wizard would pop-up when I use File|Open, but the data goes straight into the worksheet. (Presumably because it is not 'delimited'?) Without the Wizard at my side, how do I get each comma-separated field in its own column? -- Terry, West Sussex, UK Try renaming your .csv file to .txt Terry Pinnell wrote: > > Could someone remind me how to handle CSV files please? I had assumed > the Text Import Wizard would pop-up when I use File|Open, but the data > goes straight into the worksheet...

Problem access variable in On Format
Hi, I'm using Access via Office XP Pro. I am trying to format the Zip Code on the detail line of my report. I have tried the following lines of code in both the On Format event and the On Print event: If len([PostalCode]) > 5 Then : : end if or if len(Me.PostalCode) > 5 Then : : end if In both events and either code, I receive the error message: Access can't find the field 'PostalCode' referred to in your expression. If I put "PostalCode" as the source of the report's control...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Adding additional rows for data entry
I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

How can I have more than 64000 rows in one sheet?
I want to import an access database in one excel sheet, it requires 200.000 rows. Any suggestions? "blafblaf" <blafblaf@discussions.microsoft.com> wrote in message news:C3D0F75D-7F4E-4F76-95F3-72D7F3AE8D7A@microsoft.com... >I want to import an access database in one excel sheet, it requires 200.000 > rows. Any suggestions? Hi BlafBlaf, The current worksheet row limit of 65536 is not expected to be increased in the immediately foreseeable future. To import your database to Excel, it would therefore be necessary to pass 64k tranches to each of four worksheets. This...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

How do i sort rows randomly?
I want to choose 50 random rows from 10,000 lines of data and paste it into a new sheet. The only way I know is to use a random number generator to randomly select the records and then copy/paste the data out out, row by row, fifty times, which is time-consuming. Is there a way to randomize my entire data table by row so that I can take the first fifty rows all at once and know that they've been randomly selected? Thanks. Jeremy Jeremy wrote: > I want to choose 50 random rows from 10,000 lines of data and paste it into a > new sheet. The only way I know is to use a random numb...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

HIGHLIGHTING CELLS #3
Hi I have a spread sheet in which I want to highlight certain cells automattically. ie B4 = 39 C4 = 38 how do I get Excel to highlight the figure in C4 if it is less than B4, someone said it was something to do with exceptions but I cannot find any details on that. Hope someone can help Dave You can use conditional formatting... 1) Select C4 2) Format > Conditional Formatting > Formula Is 3) Enter the following formula: =(C4<B4) Note that if you don't want C4 highlighted when it's empty, use the following formula instead... =(C4<>"")*(C4<B4) ...

Insert new row as cell contents change
Insert new row as cell contents change. After importing data I have a spread sheet with a column that contains a series of alpha numeric characters. At various random intervals in this column the contents change. EG rows 1 to 4 could contain ABC, then rows 5 to 15 could become 222. I am looking for a method to insert a blank row automatically between the rows were the contents change. Many Thanks Geo George If you are familiar with VBA the code below will do what you want. Preselect the column of data first Sub InsertRowAfterValueChange() Dim myCell As Range Dim sCurrVal As String ...