'Default value in data validation dropdown

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

=WEEKNUM(TODAY())

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.

google-sheets google-sheets-formula

Solution 1:^[1]

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.

Solution 2:^[2]

Suggestion

As what @player0 have mentioned, this is possible using a script. You may use this quick sample script below as reference on how to create a drop-down, set it with week numbers from the current week down to the first week of year, and set it up to show the default current week number.

Sample Script:

function test() {
  var currentDate = new Date();
  var startDate = new Date(currentDate.getFullYear(), 0, 1);
  var days = Math.floor((currentDate - startDate) / (24 * 60 * 60 * 1000));
  var weekNumber = Math.ceil((currentDate.getDay() + 1 + days) / 7);
  var weeks = [];

  while (weekNumber != 0) { //Get the week numbers from today to the first week of the year
   weeks.push(weekNumber)
   weekNumber--;
  }

  var cell = SpreadsheetApp.getActive().getRange('A1');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(weeks, true).build();
  cell.setDataValidation(rule).setValue(weeks[0]); //Sets the default value to the current week number
}

Test

Reference

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	The God of Biscuits
Solution 2	Irvin Jay G.