Highlighting fields based on a dropdown in Excel 2003
Hi!
I have a spreadsheet in Excel 2003 that documents some processes that we follow. *1 specifies a number of different processes that we do. A* specifies the different job types that we do. B2:M11 contains some combination of four charcters ("1234") specifying different technologies, based on whether or not the process in (A:*) applies to that technology. For example, if the process in A2 only applies to technology 2 and 4, B2 will contain "24" I'd like to add a dropdown to the sheet containing the four technologies. When a user makes a selection via the dropdown box, I would like to highlight any field on the form containing characters related to that technology. An example: Job A2 uses processes B2 "12" , C2 "124" , F2 "24" Job A3 uses processes C3 "123", D3 "4", G3 "12" Job A4 uses processes C4 "24", D4 "1", E4 "2" ....and the user selects technology 2 from the dropdown. I want the following cells to be highlighted: B2, C2, F2, C3, G3, C4, E4 Is this possible? If so, how? |
Highlighting fields based on a dropdown in Excel 2003
Assuming your dropdown list is in cell A1:
- select all cells - select Conditional Formatting from the Format menu - change 'Cell Value Is' to 'Formula Is' - in the text box enter =(FIND($A$1,A1))0 - click the Format button - select the options you want to highlight the cells - click OK until all the dialogs are closed If your dropdown list is in a different cell, change $A$1 to that cell (keep the dollar signs so it remains an absolute reference). Hope this helps, Hutch "Calab" wrote: Hi! I have a spreadsheet in Excel 2003 that documents some processes that we follow. *1 specifies a number of different processes that we do. A* specifies the different job types that we do. B2:M11 contains some combination of four charcters ("1234") specifying different technologies, based on whether or not the process in (A:*) applies to that technology. For example, if the process in A2 only applies to technology 2 and 4, B2 will contain "24" I'd like to add a dropdown to the sheet containing the four technologies. When a user makes a selection via the dropdown box, I would like to highlight any field on the form containing characters related to that technology. An example: Job A2 uses processes B2 "12" , C2 "124" , F2 "24" Job A3 uses processes C3 "123", D3 "4", G3 "12" Job A4 uses processes C4 "24", D4 "1", E4 "2" ....and the user selects technology 2 from the dropdown. I want the following cells to be highlighted: B2, C2, F2, C3, G3, C4, E4 Is this possible? If so, how? |
Highlighting fields based on a dropdown in Excel 2003
"Tom Hutchins" wrote in message ... Assuming your dropdown list is in cell A1: - select all cells - select Conditional Formatting from the Format menu - change 'Cell Value Is' to 'Formula Is' - in the text box enter =(FIND($A$1,A1))0 - click the Format button - select the options you want to highlight the cells - click OK until all the dialogs are closed If your dropdown list is in a different cell, change $A$1 to that cell (keep the dollar signs so it remains an absolute reference). That works great... but I just discovered one more thing we need that I can't figure out... I also need to check for a literal value as well. =(FIND($A$1,A1))0 OR (FIND("*",A1))0 Which doesn't work. : ( |
Highlighting fields based on a dropdown in Excel 2003
Hi Tom
Because the "*" is a wildcard, if you want to search for the "*" itself, you need to prefix it with the tilde "~" You also have the syntax for the OR function incorrect. =OR(FIND($A$1,A1)0,FIND("~*",A1)0) -- Regards Roger Govier "Calab" wrote in message ... "Tom Hutchins" wrote in message ... Assuming your dropdown list is in cell A1: - select all cells - select Conditional Formatting from the Format menu - change 'Cell Value Is' to 'Formula Is' - in the text box enter =(FIND($A$1,A1))0 - click the Format button - select the options you want to highlight the cells - click OK until all the dialogs are closed If your dropdown list is in a different cell, change $A$1 to that cell (keep the dollar signs so it remains an absolute reference). That works great... but I just discovered one more thing we need that I can't figure out... I also need to check for a literal value as well. =(FIND($A$1,A1))0 OR (FIND("*",A1))0 Which doesn't work. : ( |
Highlighting fields based on a dropdown in Excel 2003
Thanks, Roger. Actually, that was the OP's formula, not mine. When I tried
your corrected formula (Excel 2003), it didn't work. This version seems to work reliably for me: =OR(IF(ISERROR(FIND($A$1,A1)),0,FIND($A$1,A1))0,I F(ISERROR(FIND("*",A1)),0,FIND("*",A1))0) Hutch "Roger Govier" wrote: Hi Tom Because the "*" is a wildcard, if you want to search for the "*" itself, you need to prefix it with the tilde "~" You also have the syntax for the OR function incorrect. =OR(FIND($A$1,A1)0,FIND("~*",A1)0) -- Regards Roger Govier "Calab" wrote in message ... "Tom Hutchins" wrote in message ... Assuming your dropdown list is in cell A1: - select all cells - select Conditional Formatting from the Format menu - change 'Cell Value Is' to 'Formula Is' - in the text box enter =(FIND($A$1,A1))0 - click the Format button - select the options you want to highlight the cells - click OK until all the dialogs are closed If your dropdown list is in a different cell, change $A$1 to that cell (keep the dollar signs so it remains an absolute reference). That works great... but I just discovered one more thing we need that I can't figure out... I also need to check for a literal value as well. =(FIND($A$1,A1))0 OR (FIND("*",A1))0 Which doesn't work. : ( |
All times are GMT +1. The time now is 11:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com