+ Reply to Thread
Results 1 to 3 of 3

lookup function

  1. 01-20-2005, 01:36 PM #1
    artstraw
    artstraw is offline
    Registered User
    Join Date
    01-20-2005
    Posts
    3

    Smile lookup function

    hi. i have made a roster for my boyf's work shifts, using excel. imy boyf is unavailable to work every other weekend (due to child access arrangements). i have a list of these dates as sheet 2 to the main roster.

    i would like to be able to somehow use the vlookup function, to make it so that if my boyf tries to put himself down to work on those days, the text will come up red, or VALUE# or something angry like that.

    i know this will involve the use of lookup / if functions, but i cant remember how to apply them (also i dont know whether a formula can make text go red). i am going to enclose a copy of my file as an attachment, if this site will let me (my boyf is A6).

    any thoughts on a formula achieving this would be very welcome, as i am trying to surprise him with it. thanx, Kris x
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-20-2005, 03:58 PM #2
    swatsp0p
    swatsp0p is offline
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Conditional Formatting will meet your needs:

    You will need to put your "blackout" dates on the same sheet (far to the right is fine)(cond. format will not reference another sheet or workbook)

    Select the desired cell (I used H11) and click Format>Conditional Formatting...

    in the pop-up window, choose "Cell is" in the Condition 1 drop down and choose "equal to" in the next box, finally, enter this formula in the next box

    =VLOOKUP(H11,$K$3:$K$29,1)
    (adjust the $K$3:$K$29 range reference to match where you placed your date list)

    Then click the Format button and choose your font color, maybe make it BOLD, too.

    Click OK twice to exit back to your sheet.

    copy this format to desired cells (copy>paste special>Formats)

    Done

    HTH
    Bruce
    The older I get, the better I used to be.
    USA
    Reply With QuoteRegister To Reply
  3. 01-20-2005, 05:24 PM #3
    pinmaster
    Guest
    Hi
    Altough conditional formatting can not reference other worksheet, it can reference named ranges so there is no need to move your "blackout" dates. All you have to do is select A3:A29 on sheet 2 and type a name in the name box (white box above column A)..... i.e dates then:

    =VLOOKUP(H$11,dates,1)

    JG
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1