I’ve previously posted about a volunteer form that I created. Apparently, there was an issue with the form, so I had to take a look.
Basically, there is a form that collects volunteers and the days that they will be volunteering. This all goes into a spreadsheet.
The spreadsheet is set up to allow for a printable sheet by each day. The day is picked in a cell that uses Data Validation. Previously, I had the data validation pulling from a range of dates. However, this meant that the dates weren’t in any real order. So, I thought about it and added a column to pull all of the dates from the range into a single column. This was done via the UNIQUE and FLATTEN commands. Thus,
=UNIQUE(FLATTEN(VDates))
was entered. This is great as it identifies UNIQUE dates, thus no duplicates, and FLATTENS the various cells into one column. (The range of cells has been named “VDates” via the Data | Named ranges menu selection. Thus M2:W is named “VDates”).
This is great, except that the dates are not SORTed. So, in the next column, I simply created a formula to sort them (
=SORT(Y2:Y,1,FALSE)).
The formula reaches into the previous column (Y), is only going to create 1 column, and FALSE is for is_ascending – i.e. this will be in reverse chronological order).
Now, the Date cell uses Data Validation that pulls from that one column. Everything is fixed and running fine.
Click to add tags…
Leave a Reply