+ Reply to Thread
Results 1 to 8 of 8

Populate Cell with another cell, if a third cell contains a non-zero

  1. #1
    Registered User
    Join Date
    06-01-2016
    Location
    California, US
    MS-Off Ver
    360
    Posts
    4

    Question Populate Cell with another cell, if a third cell contains a non-zero

    Ok, so here is what I want to do:

    I want to make a list of all the dates that contain Over Time hours in an excel sheet.

    I have already created the "month" in which all hours are calculated, and OT shows up automatically in a specific cell. for reporting/review purposes I would like to have a list created of only the dates that contain OT hours.
    attached is an image of the sheet Im working with... or at least part of it.
    excel example.JPG
    Any thoughts?

  2. #2
    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,944

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Registered User
    Join Date
    06-01-2016
    Location
    California, US
    MS-Off Ver
    360
    Posts
    4

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    Thanks for the suggestions, and corrections. I will follow up this afternoon.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    We can see the dates but which column(s) contain the OT hrs?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    06-01-2016
    Location
    California, US
    MS-Off Ver
    360
    Posts
    4

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    Attached is the sheet Im working with.

    the report section is where I would like to populate - one list according to the OT Accrued Column, one according the the COMP used column.

    Essentially creating a summary of the date and amount of OT earned or used.

    thanks! I have a simple IF statement in there now... but it just doesn't create the elegance Im looking for.
    Attached Files Attached Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    I think this does what you want.

    This array formula** entered in J4:

    =IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(E$4:E$40),ROW(E$4:E$40)),ROWS(J$4:J4))),"")

    This formula entered in K4:

    =IF(J4="","",LOOKUP(J4,B$4:E$40))

    Select J4:K4 and copy down to J36:K36

    This array formula** entered in M4:

    =IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(F$4:F$40),ROW(F$4:F$40)),ROWS(M$4:M4))),"")

    This formula entered in N4:

    =IF(M4="","",LOOKUP(M4,B$4:F$40))

    Select M4:N4 and copy down to M36:N36

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  7. #7
    Registered User
    Join Date
    06-01-2016
    Location
    California, US
    MS-Off Ver
    360
    Posts
    4

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    AWESOME!!!!

    thank you Mr. Valko! I can't tell you how much I appreciate your help!

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Populate Cell with another cell, if a third cell contains a non-zero

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Having used GetPivotData to populate cell C3 can I extract Item1 to populate cell B3
    By Hirad001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2015, 12:59 PM
  2. [SOLVED] type designate text in cell & have another cell automatically populate w/ designated #?
    By PeppiVic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2014, 03:46 PM
  3. [SOLVED] Populate a cell if another cell is greater than or equal to a third cell.
    By Tazyote in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-20-2014, 02:51 PM
  4. Replies: 1
    Last Post: 11-21-2013, 06:07 AM
  5. Have a cell automatically populate with a name if a cell matches another cell
    By rogerwphillips in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2013, 09:14 PM
  6. Replies: 0
    Last Post: 10-30-2012, 03:48 PM
  7. Macro to auto populate the destination cell based on the values of the previous cell
    By Avinash Kumar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2009, 02:06 AM

Tags for this Thread

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