Excel new table row with initial value

Question

Hello all,

I have an excel spreadsheet where I retrieve the following list values in a column (Pending, In Review, Completed). These values are given as dropdown. I now want to specify Pending as a fixed initial value for a new row. So when I start a new row in the table, that then directly as the first "Pending" is selected. How do I do this. I haven't found a solution so far via an If function, so that the formula with the dropdown menu works and the cell is not displayed as incorrect formula specification. Maybe it works much easier without a formula, just haven't found it yet.

Answer 1

Hi @Müller Timo

Take the following Table as example where the drop down should be on the [Status] column:

Proceed exactly as follow in order, assuming when you create a new row it's not by entering something in the [Task] column otherwise adjust the formula in #5 to point to another column than B:

1) Remove any existing Data Validation on column [Status]
2) Create a new sheet. Assume it's named Sheet2. Enter
in A1: Pending; in A2: In Review; in A3: Complete
3) Go back to the sheet where your Table sits
4) Select column [Status] > Data > Data Validation > Select List > Source:

=Sheet2!$A$1:$A$3  

5) In 1st row of column [Status] enter formula:

=IF(B2 = "", "Pending", Sheet2!$A$1)  

(thanks to the Table the formula should auto. copy down)

Notes:

• When you'll change the [Status] from Pending to something else in i.e. D4, that cell will keep the selected value from the drop down list only. In other words, the formula in D4 will be overwritten (removed if you pref.) by the selected drop down list option
• You can hide the extra sheet no problem