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!

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

Originally Posted by kersplash

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

Can the cell be blank but still have a formula?

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

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

Originally Posted by kersplash

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

Originally Posted by Alvin Chung

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?

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).

Please help ))