Well, after a good bit of thinking, I’ve fixed the Volunteer Form. This was the form where I created a way to track Volunteers. Since there is someone who needs to print the form, it needs to be visual and a way to select a day. You can read the write up on the Volunteer Form Update to get a bit of background.
I started by wondering if I could add a SORT command into the validation formula. That led me to thinking about this a little differently. What if instead of trying to sort all the rows and columns, I created a column that pulls from that range? So, I added a column and added the following formula into it. Then, I needed to make sure that was one column. Thus, I added FLATTEN as a command. This basically puts all of the dates into one column.
=SORT(UNIQUE(FLATTEN($M$2:W)),1,FALSE)
What this formula does is to grab all of the dates in the range (cells M2 through the end of column W) and sorts them in reverse chronological order. The UNIQUE part makes sure that I don’t have duplicate dates (because there are lots of duplicated dates). Now I can use this column for my data validation.
I still need to pull data from each of the other rows and columns, so I use the following formula for that.
=FILTER(‘Form Responses 24/25’!C2:H,(‘Form Responses 24/25’!M2:M=A2)+(‘Form Responses 24/25’!N2:N=A2)+(‘Form Responses 24/25’!O2:O=A2)+(‘Form Responses 24/25’!P2:P=A2)+(‘Form Responses 24/25’!Q2:Q=A2))
This is a basic formula that uses multiple filter criteria (basically one for each column needed).
Together, this has cleaned up the Volunteer Form to be much more user friendly.
This is also a transferable strategy. This same process can allow me to create forms and then present that information by date. I’ve used this to create an Announcement process to collect announcements. These can then be sorted and presented by date.
Leave a Reply