Search code examples
google-sheetsgoogle-sheets-formula

Default value in data validation dropdown

I have a table that changes dynamically based on the week we're on by using

=WEEKNUM(TODAY())

enter image description here

but I want to create a dropdown menu with all the week numbers, so a user can select any week and check the data related to that weeknum. However, I'd like that everytime the sheet opens, it shows as a default, the current weeknum.

Solution

  • You might be able to use a non-script approach if you can adapt the technique outlined in detail at https://www.benlcollins.com/spreadsheets/default-values/. You will need to be able to add a column to the immediate left of the 'Week' column, and probably also to hide the column afterwards for neatness.

    For instance, if the example table above was in columns B&C, add the following formula to A2, copy down the column as far as needed then hide column A:

    ={"",weeknum(today())}

    You can then set up the data validation dropdown in the week column, and anything you select in those cells will overwrite the default value. This generates #REF! errors in column A because you are preventing a literal array expanding, but you can't see the error because it's hidden, and you don't care about it anyway. If you delete the value in any cell in the Week column, the array in the corresponding cell in column A will be able to expand again and show the default value once more.