A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional more than one iif



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2010, 09:44 PM posted to microsoft.public.excel.worksheet.functions
JoeM
external usenet poster
 
Posts: 41
Default Conditional more than one iif

I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is
“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

  #2  
Old April 21st, 2010, 09:56 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Conditional more than one iif

Hi,

Try this CF formula

=AND($U1=1,$M1=30)

Select the full row before you apply it, change the 1 to the row you are in.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JoeM" wrote:

I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is
“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

  #3  
Old April 21st, 2010, 09:57 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 516
Default Conditional more than one iif

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

  #4  
Old April 21st, 2010, 10:59 PM posted to microsoft.public.excel.worksheet.functions
JoeM
external usenet poster
 
Posts: 41
Default Conditional more than one iif

I tried the both above but to no avail.

I want to select all the rows and if a value in column M are greater than 54
(54) and the value in column V is = 1 then highlight the row.

Joe


"Bernard Liengme" wrote:

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

.

  #5  
Old April 21st, 2010, 11:01 PM posted to microsoft.public.excel.worksheet.functions
JoeM
external usenet poster
 
Posts: 41
Default Conditional more than one iif

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel

No particular reaason just wantd to note the values I was looking for,
probably some of MSAccess queries rubbing off on me, long day, sorry!

Joe

"Bernard Liengme" wrote:

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

.

  #6  
Old April 21st, 2010, 11:05 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Conditional more than one iif

I tried the both above but to no avail.

Both solutions are the same and if you have described your problem correctly
both work.

If you select a range of rows then the number in the formula must be the top
row number of the selected range


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JoeM" wrote:

I tried the both above but to no avail.

I want to select all the rows and if a value in column M are greater than 54
(54) and the value in column V is = 1 then highlight the row.

Joe


"Bernard Liengme" wrote:

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

.

  #7  
Old April 22nd, 2010, 12:43 PM posted to microsoft.public.excel.worksheet.functions
JoeM
external usenet poster
 
Posts: 41
Default Conditional more than one iif

Maybe it's my CF formula?:
=AND($U1=1,$M130)


"JoeM" wrote:

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel

No particular reaason just wantd to note the values I was looking for,
probably some of MSAccess queries rubbing off on me, long day, sorry!

Joe

"Bernard Liengme" wrote:

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

.

  #8  
Old April 22nd, 2010, 01:22 PM posted to microsoft.public.excel.worksheet.functions
JoeM
external usenet poster
 
Posts: 41
Default Conditional more than one iif

The formula works fine, but what i am finding out is if I start my sheet at
say A45 the formula doesn't seem to get the same results, where if I start
the sheet at A1 it works fine.

Joe

"JoeM" wrote:

Maybe it's my CF formula?:
=AND($U1=1,$M130)


"JoeM" wrote:

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel

No particular reaason just wantd to note the values I was looking for,
probably some of MSAccess queries rubbing off on me, long day, sorry!

Joe

"Bernard Liengme" wrote:

No IF needed
Let's say you want this to apply to rows 2 thru 20
Select the row headers 2:20 so that the entire range is highlighted
For you Conditional Format formula use =AND($M230,$U2=1)
We reference cells in row 2 here because that this the first row of our
selected range
Note that the formula used in conditional formatting generally should return
TRUE or FALSE

BTW: why in your question did you but 1 and 30 in quotes. We seldom need
quotes around numbers in Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"JoeM" wrote in message
...
I need to conditional format based on if two cells are evaluated:

If in this row the value in column U is = “1” and the value in column M is

“30” then row should be highlighted green

I probably should not wait til the end of the day to start a new excel
sheet...lol


Joe

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.