+ Reply to Thread
Results 1 to 9 of 9

Looking to populate cells based on another cell

  1. #1
    Registered User
    Join Date
    03-21-2017
    Location
    Toowoomba, Australia
    MS-Off Ver
    365
    Posts
    10

    Looking to populate cells based on another cell

    Hi everyone, I'm new to this forum and just looking for some help.

    I'll try to explain it as best I can but please let me know if you require any more information. I'm slowly learning formulae for excel and a little on Google Sheets. Normally when I need help I just Google what I'm after and I know enough to be able to adapt the formulae to what I need it for, no luck this time.

    On one sheet I have my Job Numbers, which in the "Test Excel" file I have in Dropbox is incomplete but has the general idea of what I'm looking to do. The other sheet is a Look Ahead Schedule that I want to populate based on the information on the Job Numbers sheet. But I only want certain jobs to be carried over, so I have a column that when filled with an "X" will fill out my Look Ahead Schedule but if the job in Job Numbers doesn't have an "X" in the column the Look Ahead Schedule registers a blank cell. I'm wating to remove the blank cells or use a different formulae so it doesn't put blank cells on the Look Ahead Schedule. (By blank cells I mean empty cells with the formulae in it)

    The formulae I'm using currently is:
    =IF('Job Numbers'!B3="X",'Job Numbers'!A3,"")

    And then change the cell references in that formulae for some other columns.

    Here is the Dropbox link:
    https://www.dropbox.com/s/pue0wg5568...xcel.xlsx?dl=0

    The actual Job Numbers list is over 400 cells long so I tried to make it less clustered with a new test file.

    Any help, even if its a link to another forum post would be great and sorry if it has already been asked, I couldn't find anything on here or the many Google searches I tried.

    Thanks in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Looking to populate cells based on another cell

    Hi,

    There is probably a few crazy formulas that can be created to return what you are wanting.
    However, I think you might be better served with implementing a Pivot Table into your Look Ahead Schedule sheet.

    In place of your Name and Number column you insert a Pivot Table, and filter the table for the X.

    In preparation for inserting the Pivot Table you would need to add 4 or 5 columns to the left of your data in the Look Ahead Schedule sheet.

    Once you have to Pivot Table in place you can delete the unnecessary columns.

    Attached is a modified version of your file with a Pivot Table in place.

    Hope this makes sense and is helpful.

    Cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-21-2017
    Location
    Toowoomba, Australia
    MS-Off Ver
    365
    Posts
    10

    Re: Looking to populate cells based on another cell

    Thanks for the reply, I've never even heard of a pivot table but I'll have a better look at it and see how I go from there. Looks like it's a concept I can grasp. Thanks again.

  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: Looking to populate cells based on another cell

    Bahal_tb welcome to the forum

    Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites.

    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

  5. #5
    Registered User
    Join Date
    03-21-2017
    Location
    Toowoomba, Australia
    MS-Off Ver
    365
    Posts
    10

    Re: Looking to populate cells based on another cell

    Okay, thanks for that. Understand completely, here is the attached file for any future needs.

    Edit: This is my original file, before southward gave me his reply.
    Attached Files Attached Files

  6. #6
    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: Looking to populate cells based on another cell

    Having said that, and not seeing your file, if you already have a column where you "X" what you wantshown, an additional helper column next to that will allow the use of a fairly simple formula to do your extract. Something likethis...

    A
    B
    C
    D
    E
    1
    Item use Item
    2
    aa x
    1
    aa
    3
    bb
    1
    cc
    4
    cc x
    2
    ee
    5
    dd
    2
    gg
    6
    ee x
    3
    ii
    7
    ff
    3
    8
    gg x
    4
    9
    hh
    4
    10
    ii x
    5
    11
    jj
    5

    Helper
    C2=COUNTIF($B$2:B2,"x")
    copied down

    Extract
    E2=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$C:$C,0)),"")
    copied down (and across) as needed

  7. #7
    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: Looking to populate cells based on another cell

    Thanks for the file :0

    Based on that...
    Job sheet
    F3=COUNTIF($B$3:B3,"x")
    copied down

    Look sheet
    A5=IFERROR(INDEX('Job Numbers'!$A:$E,MATCH(ROWS($1:1),'Job Numbers'!$F:$F,0),MATCH(A$4,'Job Numbers'!$A$2:$E$2,0)),"")
    copied down and across as needed.

    NOTE - the headings on both sheets MUST match, for this to work

  8. #8
    Registered User
    Join Date
    03-21-2017
    Location
    Toowoomba, Australia
    MS-Off Ver
    365
    Posts
    10

    Re: Looking to populate cells based on another cell

    Thanks for the replies, that formulae is working wonderfully and the good news is I was able to adapt it from your post without the file and it matches what you've got now. Well I didn't expect to get so much help so quickly. I really appreciate everyone's help.

    Thanks!

  9. #9
    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: Looking to populate cells based on another cell

    Thats awesome, happy to help

    Take a look at Pivot Tables too though, they are just another tool available to you

    If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ 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. Auto populate cell based on another cells data
    By shapmb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-23-2015, 09:45 PM
  2. [SOLVED] Can I Auto populate cells based on value in another cell
    By Rednecknerd in forum Excel General
    Replies: 2
    Last Post: 12-11-2013, 10:58 AM
  3. [SOLVED] Populate Cells based on Criteria of Another Cell
    By mkeys4 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-16-2013, 07:39 AM
  4. populate a cell based on conditions in other cells
    By sa02000 in forum Excel General
    Replies: 3
    Last Post: 06-25-2013, 08:52 AM
  5. Automatically populate cells based on values in another cell
    By mweick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2013, 02:47 PM
  6. Populate a cell based on another cells condition
    By verder27 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-23-2012, 12:36 AM
  7. Populate a cell based on 4 possible outcomes in two other cells
    By AJLowther in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2009, 07:17 AM

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