+ Reply to Thread
Results 1 to 7 of 7

If Statement with Multiple Sheets

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    ntronloy teacher
    MS-Off Ver
    Excel 2011
    Posts
    13

    If Statement with Multiple Sheets

    Hello all,

    I am new to this forum, so thank you for helping me out. I am trying to create an if-statement for a sheet i am making, and I have attached a sample sheet of what I am trying to do. As you see, on "Sheet2" in cell B4 I am trying to create an If-And statement. What I want to do is display the cell A1 on "Sheet1" if the name and the date are the same in both sheets. The idea is to have each cell under "location" format the if statement so if the name in column A of "Sheet2" matches up with the name in column a of "Sheet1" AND the date in column b of "Sheet2" matches up with the date of column b of "Sheet1"...Ultimately, I want to have this formula in all rows and columns of the week so it automatically formats where the employee is working.
    Attached Files Attached Files
    Last edited by etulipano; 11-24-2012 at 06:54 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If Statement with Multiple Sheets

    job site ?you just have a bunch of time ranges, have you a more realistic example filled in as you want to see it?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: If Statement with Multiple Sheets

    Basing on your formula in B4 of your second sheet, put this in B4. Copy across and down.

    =IF(AND(MATCH($A4,Sheet1!$A$6:$A$18,0),Sheet1!B$5=Sheet2!B$2),"AMD")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If Statement with Multiple Sheets

    I think you need to use index/match for this, although how/where location comes into it, i have no idea. see if this will give you what you want?

    =INDEX(Sheet1!$A$5:$H$18,MATCH(Sheet2!$A4,Sheet1!$A$5:$A$180,0),MATCH(Sheet2!B$2,Sheet1!$A$5:$H$5,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    ntronloy teacher
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: If Statement with Multiple Sheets

    haha, sure thing. I want the location of the site in the "All" tab, under the "Location Column" with the site location being in A1 of each other sheet. I have a total of 18 sites, but i provided 7. In each "in" and "out" column, I want the time correlated with the day they employee works. For Example: if Tom works at Site 5 from 0800-1600 on Friday. i want Tom's name in the "All" sheet to show him working at location "Site 5" from 0800-1600 under Wednesday.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-24-2012
    Location
    ntronloy teacher
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: If Statement with Multiple Sheets

    you can delete the "matt" and formula on the "add" sheet. I forgot to delete it

  7. #7
    Registered User
    Join Date
    11-24-2012
    Location
    ntronloy teacher
    MS-Off Ver
    Excel 2011
    Posts
    13

    Re: If Statement with Multiple Sheets

    My idea is to just fill out the schedules for each site, and the "All" sheet automatically fills in, therefore my employees just have to find their name and can see which site and what times they are working, rather than going through each site schedule to see if they are working.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If Statement with Multiple Sheets

    I'm kinda understanding what you want, but it would be a lot easier to figure out if you added some dummy data to the "sites", and included a few examples on the ALL sheet showing what you're expected outcome is, and how you arrived at that (if it's not obvious)

+ 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