Entering Data that willl search and return informative information----- New Problem

I received great feedback on the original posting. Today, I was informed that our color-coded solution wouldn't work for everyone. As a result, I--attempting to figure it out--attempted to apply the same logic so that Information Regarding AIRPORT SIZE (HUB, LARGE AIRPORT, MEDIUM AIRPORT, SMALL AIRPORT) and SITE SIZE (MAJOR SITE or STANDARD SITE) will pull into cells J7 or J20.

In short, when the user enters a code in D16 or 19 the spreadsheet would go to the VLOOKUP tab search the content and brings back color coded Airport/Site name & (newest request) Airport/Site size. I can't thnk my way into a solution. Can anyone help?

LOL.... DUH! I was close. I'm pleased. Thank you daffodil11. I couldn't have gotten that far without your help. Thanks for the fix.

As for changing the Conditional Formatting:

Click J16, choose Conditional Formatting -> Manage Rules

Next to each rule is the range is applies to. Change each of these to =$J$16:$J$17

Now, double click each rule and change the formula from

=IF(VLOOKUP(D16,VLOOKUP!$B$3:$D$1201,3,0)<2,TRUE,FALSE)

to

=IF(VLOOKUP($D$16,VLOOKUP!$B$3:$D$1201,3,0)<2,TRUE,FALSE)

We're just adding absolute references to D16, so that both cells base their formatting off of D16. Without the absolutely references implied by using $, J17 will run the same formula off of values in D17 and we don't want that.


Afterwards, repeat the same thing again for J19, by adding J20 to the range, and changing each formula to have absolute reference to D19

Also, you can toggle absolute references by highlighting a range, such as "A1" in a formula or "B5:B10", and then hit F4.

This keystroke will put the $ in place. Hitting it again will remove the absolute reference from the column, hitting it again will alternate to column but not row, and hitting it a third time will remove the $ symbols again.

Here's the finished product in case you get stuck and need a point of reference.

airports and stuff.xlsm

Something is broken when entering code.

CLT pulls wrong colors; should be orange for airport and red for site

IAH & ORD pulls correct color but adds color in the AIrport/SIte Name fields ... should be no color but nothing should happen at all in SITE

MOC pulls perfectly

I'm checking formulas can't figure out what I did wrong.

Disregard last post. I see how I went wrong.