Vlookup and Match

**JBC** · 09-12-2009, 02:46 PM

I have attached a spreadsheet that contains a sheet "Schedule" on this sheet it has Columns Monday-Sunday. I want on sheets "Monday"-"Sunday" to contain the info on the "Schedule" sheet only if the employee is working on Monday, etc.

If "Schedule"$C8:C40= any value other than Off, then "Monday"$B4:c4="Schedule"$B8;C8

I know this is incorrect formula, but I don't know how to use the lookup function.

Thanks in advance for your help.

**JBeaucaire** · 09-12-2009, 04:36 PM

Simplest solution of all is to apply a Conditional Formatting to the daily sheets.

For instance, you already had TUESDAY setup to simply reflect all the values from the Tuesday column on the Schedule. That's fine, leave that.

1) Highlight the entire table from C4:E32
2) Select Format > Conditional Formatting
3) Use Condition1: > Formula Is: > =OR($C4="OFF",$C4=0)
4) Click on Format... > Font > Color: and choose the solid white font
5) Click OK > OK

Now, all the cells that have "OFF" or "0" in column C will turn invisible so you will only see the names listed with actual shifts.

This will show blank rows, but that's not automatically bad, so this is the simplest approach of all.

**JBC** · 09-12-2009, 06:45 PM

Thanks for your help. Do I need to highlight the B column as well? This is the column that contains the names. Also do I leave the formulas that I already have in the cells
B4;E32? Where do I select Format> Conditional Formatting? Is this under new formatting rules?

Thanks again for your help!

**JBeaucaire** · 09-12-2009, 07:25 PM

You'll have to find the conditional formatting section for 2007 since i use 2003, I don't know where it is and I know they confused everyone with the 100% new layout to so much stuff.

Yes, of course, include B in the selected range.

**JBC** · 09-16-2009, 10:29 PM

I have tried this formula and it doesn't seem to give me what I am needing. I want the items on sheets labeled Monday-Sunday to only contain the names of employees that are scheduled to work to show on these sheets. If they are off I do not want them to appear on the sheets Mon-Sun. Do you have any suggestions if this is possible?

Thanks for your help.

**JBeaucaire** · 09-17-2009, 12:59 AM

Yes, my instructions in post #2 explicitly provide for the requested results.

Here's your sheet back, I inserted the conditional formatting on all the daily sheets using the provided steps. It has empty rows where the missing employees are, but that's not automatically an issue.

I turned on the AUTOFILTER on each sheet, so if you wish to shorten the list, just click on the down arrow for the employee column and select non-blanks.

We haven't introduced any VBA, but we could add a worksheet_activate macro on each of the daily sheets that hid all the blank rows for you making the manual drop downs unnecessary. Just a thought.

**JBC** · 09-17-2009, 11:41 AM

Thanks so much for your help! This worked out perfect. I don't understand how it worked but it does. I selected the drop down on employee to get rid of the blanks (select non-blanks) I could not find "non-blanks". What am I doing wrong??

Thanks for your help. Greatly appreciated!!
JBC