+ Reply to Thread
Results 1 to 9 of 9

Autopopulate formula in a column ONLY IF the adjacent columns are filled

  1. #1
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Hi All,

    Awesome community, hoping someone can help.

    Basically what I'm trying to do is have a formula in column E of the below autopopulate as soon as information is entered in the columns preceding it (Columns A, B, C, D)

    I cannot have the formula in that column UNTIL there is information in the adjacent columns, as that throws off something else I'm doing. So if columns A, B, C, and D are blank, column E should be too.
    Currently column E's formula is a simple Vlookup using a 2nd sheet in excel (google sheets)...the formula is below:


    =VLOOKUP(D3,Sheet2!$A$1:$B$3,2,0)

    The Vlookup is pulling an individual's name (the provider) based on the state that is entered in column D. The table I am using is in sheet 2

    The reason why column E needs to remain blank is because I have a trigger that is sending an auto email as soon as a "new row" is entered in my document. So I can't just prepopulate column E with my formula all the way down the column and "wait" for the adjacent columns to be filled in, because even if they are then filled in, no auto email trigger will take place (because excel "thinks" that isn't a new row as it already contained data in column E).

    Hope that makes sense. Little screenshot below:
    excelforum.PNG

    Sheet 2 where the info is for the Vlookup:
    excelforum2.PNG

    Any idea if this is possible... if you need more info from me, I'm happy to provide. Thanks everyone!

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    If you hold your data in a table then the formula will auto-fill with a new row.

  3. #3
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Quote Originally Posted by kersplash View Post
    If you hold your data in a table then the formula will auto-fill with a new row.
    Not that simple unfortunately...

    In my screenshot, I want the 3rd row to be empty and thus cell E3 to be empty (no formula) UNTIL information is entered in columns A through D (which will happen all at once as its pulling from an online form and uploading into my worksheet, with data from rows A through D. As soon as that happens, I need the formula from cell E2 (the vlookup formula) to be copied down to cell E3... but NOT BEFORE THAT MOMENT. Cell E3 needs to stay empty until the adjacent cells are filled.

    hope that makes sense

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Can the cell be blank but still have a formula?

    ie. =IF(cell="","",your formula)

  5. #5
    Registered User
    Join Date
    06-07-2019
    Location
    Singapore
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    5

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Just in case you're not familiar with kersplash suggestion, you can format your data as a table by selecting the data (e.g. A1:E100) and click on Home >> "Format as Table"
    Once your data is formatted into a table, column E will be auto-populated every time you try enter something in new row

    --------------------------------------------------------------------------------
    Please click on the small star icon below the post if the response helps

  6. #6
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Quote Originally Posted by kersplash View Post
    Can the cell be blank but still have a formula?

    ie. =IF(cell="","",your formula)
    Just tried this... unfortunately it doesn't work... the cell cannot contain any data at all or excel "thinks" the row has already been created and does not trigger as a "new row" with new data, and doesn't auto send the email. Thought this was gonna work, argggggg

  7. #7
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Quote Originally Posted by Alvin Chung View Post
    Just in case you're not familiar with kersplash suggestion, you can format your data as a table by selecting the data (e.g. A1:E100) and click on Home >> "Format as Table"
    Once your data is formatted into a table, column E will be auto-populated every time you try enter something in new row

    --------------------------------------------------------------------------------
    Please click on the small star icon below the post if the response helps
    I'm not sure I fully understand this, but either way, are we sure this will fix the actual issue I have or solve the problem etc?

  8. #8
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Does anyone have a solution for this? I've tried everything... basically I need a way to bring a formula down from a row above, but only as soon as there is information filled in on the new row...

    NO data can be in the formula cell (cell E) before there is information in the adjacent cells (cells A through D in my example).

    So the workflow is person enters information in a form, that form is linked to excel and when they submit it, it creates a new worksheet row... (cells A through D). I then need cell E to populate using the vlookup, but cell E cannot contain the formula for the vlookup UNTIL cells A through D are filled in (ie after they individual submits the form).

  9. #9
    Registered User
    Join Date
    06-12-2019
    Location
    new york, NY
    MS-Off Ver
    Excel
    Posts
    6

    Re: Autopopulate formula in a column ONLY IF the adjacent columns are filled

    Please help ))

+ 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