+ Reply to Thread
Results 1 to 7 of 7

Vlookup and Match

  1. #1
    Registered User
    Join Date
    09-10-2009
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Vlookup and Match

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup and Match

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-10-2009
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Vlookup and Match

    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!

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup and Match

    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.

  5. #5
    Registered User
    Join Date
    09-10-2009
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Vlookup and Match

    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.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup and Match

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-10-2009
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Vlookup and Match

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1