Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Function - conditional formatting but for an array of data 1

Status
Not open for further replies.

CTaylor1968

Vendor
Aug 5, 2005
35
GB
I have created a spreadsheet based on new applications received. I want to have a column that gives the name of a school and whether there is a vacancy (Y or N). Another column records all applications received and lists which school the applicant would like to be appointed to. I want to put in a formula in the column that gives the applicant's preferred school to look up the list of vacancies at a school (2 columns), and if there is a vacancy to highlight the column. So for example, column A would list the schools, column 2 would list Y or N for vacancy, column S would list a school that an applicant wants to be appointed to. So if column S includes a school that has a Y against it, it will highlight. Does this make sense? Can somebody help me with this please? Thanks ever so.
 
Highlite the entire S column, then select conditional formating from the menu.

Then change "cell value" box to "formula is" click the formula box (the little icon on the right) to activate
and paste this =IF(VLOOKUP(S1,Y:Y,1,FALSE)=S1,TRUE,FALSE)

...set the format for when the condition is true
 
It didn't work. Okay, I've set the spreadsheet up now. One half of the spreadsheet records the applications received whilst the other half keeps constant track of the vacancy level of a school (we appoint governors). So as it stands, Column C is where I type in the name of the school that the person wants to be appointed to. Column I lists the schools we have and column K is the Y or N column to say if a vacancy is at the school. So now if I type in the name of a school that is has a Y in column K next to the appropriate school, it will highlight as set. Or maybe, you can think of an easier way? Basically I want to flag up if a vacancy comes up at a school and we have somebody who has applied to that school. Any help appreciated.
 
Actually I got that the wrong way around. I want to type in a Y in column k (column J lists the school names) if a vacancy becomes available and if that school is listed in column C then column K will highlight.
Again, if there is a better and more simple way....feel free.
 
Highlite the entire column K, then select conditional formating from the menu.

Then change "cell value" box to "formula is" click the formula box (the little icon on the right) to activate
and paste this =IF(AND(K1="Y",MATCH(J1,C:C,0))>0,TRUE,FALSE)

...set the format for when the condition is true


 
Thank you. This partially works but it is automatically highlighting rather than highlighting only if I place a Y in the cell in column K.
 
Ooops.. i fumbled a paren, try this formula

=IF(AND(K1="Y",MATCH(J1,C:C,0)>0),TRUE,FALSE)
 
Fantastic - thank you.
Presumably I will just repeat this formula if I decide to go the other way (eg highlight the cell when somebody applies).
Thanks for all your help.
 
If you highlited the entire column...the condition is auto applied to all cells in the column relative to the actual row being edited.

 
...that is to say the formula automaticaly changes from k1, j1 etc...to relect the active cells row.
 
CT, I'm assuming you found ETID's post to be helpful, and that they fixed your problem. Hopefully you also noticed the text bottom left under each of ETID's posts :)

Regards
Ken...............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Yes to both questions. It was very helpful and also very speedy! I now have a spreadsheet set up that I've been trying to figure out for three days.

Many many thanks.
 
LOL - The gist of that was that if you find a post helpful, the usual way of acknowledging it is to click on the link below their post which will mark the post with a star. This increases the poster's star count and also flags the post for other people such that they can be reasonably sure that the question got answered with something useful.

Regards
Ken...............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top