Using a drop down list, in an Array Formula - Google Sheets

1
votes

I am trying to create a drop down list to either select whether a Risk is Open or Closed. The data in Google Sheets comes from a Google form.

I am using the Array Formula (because new rows are added through the form which removes any previous formulas.

This is the code I have used so far:

=ARRAYFORMULA(IF(LEN(D2:D300),E2:E300,Sheet3!B3:B4))

Where Sheet3 has my 'Open' and 'Closed' options as the 2 drop down choices.

Can anyone help me :(

enter image description here

1
google-sheetsarray-formulasgoogle-forms
What do you want to have in the E column ? Do you want it as either OPEN or CLOSED ? And how do you know the RISK or ISSUE is open or closed ? – nabais
For drop down list go to Data/Data Validation in Cell range Select E2:E. In Criteria list from a range and select range on Sheet3 where Open and Closed options are. – Oleg_S
@nabais - the form is used to initially raise the Risk or Issue. They would automatically be Open once the form containing the Risk or Issue is submitted. Then it would be reviewed in a meeting where the manager would select the drop down to close a Risk or Issue. It needs to be Open as default but then able to close without impacting the other cells in the column. – LavaSlam1989
Column E should be a drop down that is either Open or Closed. Doing this manually wouldn't work because the form enters a new row and removes any previous code. – LavaSlam1989

1 Answers

1
votes

From what I can understand, you can't do an arrayformula with the default value as the arrayformula will automatically rechange it (dynamic formulas).

However, you can just let it be either open or closed, as @OlegS said in the comment.

You should do the following:

  • click on data => data validation
  • the range should be: 'YourSheetName'!B2:B
  • the List from a range criteria should be: Sheet3!B3:B4
  • then click Save

You should have the tick buttons on the row. If you go to any cell and click enter, it will tell you to choose the values you want (either open or closed).

enter image description here