• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Coditional formatting to indicate a server is on another list...

T

theshooter

New Member
Hi -

I am trying to do something that is probably simple, but I am struggling with exactly how to accomplish it.


I have two worksheets, both in the same workbook, Excel 2007, and the first worksheet is for detailing firewall changes. The second worksheet is the list of all the servers that have disaster recovery impact.


I want the cell in the first worksheet to change color, if the server they type in matches any of the servers on the other worksheet, change color to indicate they need to consider disaster recovery when asking for firewall changes.


I am not a formula wizard, so I am sure there is a simple formula to apply, but I need ideas on how to solve.


The list of servers does not have the servers in the first column, so not sure VLOOKUP would be right answer.???


Any guidance/examples would be deeply appreciated.


Thanks!!

J
 
Use this formula.

=MATCH(lookupvalue,lookuprange,0)

Example:

=MATCH(O3,L2:L4,0)


This formula will let conditional format know if a match is found therefore making the statement true which will color your cell


Put the formula in the conditional format formula bar obviously. Make sure to Lock your range too.
 
Hi, theshooter!


I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Let's say your server name is in first sheet Sheet1 in cell A1, your server list in second sheet Sheet2 in column A.


Select cell A1 in first sheet, Home tab, Styles group, Conditional Formatting icon. New Rule, Use Formula (last option) and type in the text box:

=NOT(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,False)))

Click on Format button, Fill tab, choose desired color, Accept, Accept.


Hope it helps.


Regards!

Regards!
 
@Montrey

Hi!

Am I wrong or your formula returns a number if value exists in list and a #¡N/A! error value if it doesn't exist? Formula result for CF should return True or False.

Regards!
 
For some reason Conditional Formatting won't let me set range to a different worksheet...


Error: you cannot use references to other worksheets or workbooks for conditional formatting.


BUT - if you name the ranges, then it accepts the formula.


THANKS!!
 
SirJB7 - thanks for the guidance - you're right, I jumped the gun a bit.


I will take a look at the stickies, though I did do a search and could not find what I was looking for, but then again, I am not sure I know exactly what I was looking for. :)


That said, I think I have a solution, but still tinkering around as I am not the owner of the firewall form, so I may have to find another solution.


Reading stickies now... ;-)


Thanks!!
 
Hi, theshooter!

Glad you solved it. Thanks for your feedback. At least in 2010 version it doesn't raises any error. Welcome back whenever needed or wanted.

Regards!
 
@Montrey

Hi!

I wasn't aware of that, everyday I learn something new. But even being aware now, I wouldn't let that assumption so implicitly stated, I rather choose to read at a glance a formula with expected results of T/F, not value(as true)/error(as false). Just personal choice.

Regards!
 
@theshooter


Glad to hear it worked! Feel free to ask more questions in the future. We love challenges
 
You must log in or register to reply here.
Back
Top