Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
How do you highlight rows where it meets 2 criteria.
Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
You can use conditioning formatting to identify those rows.
Try this: Let's assume the range of interest is A1:B10. In Excel 2007 Select the *entire* range of cells, A1:B10, starting from cell A1. Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format Enter this formula in the box below: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out In Excel versions 2003 and earlier Select the *entire* range of cells, A1:B10, starting from cell A1. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "N. Vu" wrote in message ... How do you highlight rows where it meets 2 criteria. Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
Thank you! I am getting closer..For some reason, it is picking up numbers in
column B that are less than 0.25??? On a 2nd note: Is there a way in say in Column A to pickup number that DOES NOT EQUAL 10 AND in Column B 0.25 "T. Valko" wrote: You can use conditioning formatting to identify those rows. Try this: Let's assume the range of interest is A1:B10. In Excel 2007 Select the *entire* range of cells, A1:B10, starting from cell A1. Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format Enter this formula in the box below: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out In Excel versions 2003 and earlier Select the *entire* range of cells, A1:B10, starting from cell A1. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "N. Vu" wrote in message ... How do you highlight rows where it meets 2 criteria. Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
=AND($A1=10,COUNT($B1),$B10.25)
it is picking up numbers in column B that are less than 0.25??? Hmmm... I don't think that's possible! Give me some examples of numbers that are less 0.25 that are being highlighted. You might have the number format set to display 2 decimal places where the *displayed value* could be 0.25 but the true underlying value is actually something like: 0.250000231454755. Excel evaluates formulas based on the true value of a cell. The true value of that cell is 0.25 so the CF is applied. If this is what's happening then we'll have to use some rounding. =AND($A1=10,COUNT($B1),ROUND($B1,2)0.25) Is there a way in say in Column A to pickup number that DOES NOT EQUAL 10 Try this: =AND(COUNT($A1),$A1<=10,COUNT($B1),$B10.25) -- Biff Microsoft Excel MVP "N. Vu" wrote in message ... Thank you! I am getting closer..For some reason, it is picking up numbers in column B that are less than 0.25??? On a 2nd note: Is there a way in say in Column A to pickup number that DOES NOT EQUAL 10 AND in Column B 0.25 "T. Valko" wrote: You can use conditioning formatting to identify those rows. Try this: Let's assume the range of interest is A1:B10. In Excel 2007 Select the *entire* range of cells, A1:B10, starting from cell A1. Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format Enter this formula in the box below: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out In Excel versions 2003 and earlier Select the *entire* range of cells, A1:B10, starting from cell A1. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "N. Vu" wrote in message ... How do you highlight rows where it meets 2 criteria. Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
Hi,
To conditionally format your cell(s): In 2003: 1. Select the cells you want to format - in your case something like A1:D50 for example. 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =AND($A1=10,$B10.25) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =AND($A1=10,$B10.25) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "N. Vu" wrote: How do you highlight rows where it meets 2 criteria. Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do you have 2 criterias be picked up in a lookup?
Hi,
Regarding the second question try =AND($A1<10,$B10.25) as the formula -- If this helps, please click the Yes button. Cheers, Shane Devenshire "N. Vu" wrote: Thank you! I am getting closer..For some reason, it is picking up numbers in column B that are less than 0.25??? On a 2nd note: Is there a way in say in Column A to pickup number that DOES NOT EQUAL 10 AND in Column B 0.25 "T. Valko" wrote: You can use conditioning formatting to identify those rows. Try this: Let's assume the range of interest is A1:B10. In Excel 2007 Select the *entire* range of cells, A1:B10, starting from cell A1. Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format Enter this formula in the box below: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out In Excel versions 2003 and earlier Select the *entire* range of cells, A1:B10, starting from cell A1. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND($A1=10,COUNT($B1),$B10.25) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "N. Vu" wrote in message ... How do you highlight rows where it meets 2 criteria. Highlight the row where 1. Column A equals 10 AND 2. Column B is Greater than 0.25 Do you do some type of lookup/conditioning formatting rule to get it to find the rows that meet both criteria. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you have 2 criterias be picked up in a lookup? | Excel Discussion (Misc queries) | |||
how do i find the last time i picked a winner | Excel Discussion (Misc queries) | |||
Posts not getting picked up | Excel Discussion (Misc queries) | |||
Have a running total when an option is picked. | Excel Worksheet Functions | |||
Lookup with 3 criterias | Excel Worksheet Functions |