XLSTemplate: please filter appearance column based on type column

Ideas
1

First, ODK XLSForm Template is super cool.

Idea:
It would be nice if the dropdown in appearance column would only show the appearances that are valid for selected question type. Not sure if that's even possible in Google Sheets, certainly possible in Excel.

1 Like
2

Hi @Sadiq_Khoja and @LN,
I agree, esp. for all explanation tables in the template sheets, please, see

3

You could certainly setup a dynamic validation dropdown in Excel using dynamic array operators UNIQUE and FILTER - this is reliant on using O365 or Excel 2021 though.

To achieve the same functionality in Excel 2007-2019 you would have to setup named ranges and use the INDIRECT operator in the validation, which is more complicated and trickier to modify but will run on older versions that are more prevalent.

Then of course you need to ensure that this works in web as well as desktop excel and google sheets or maintain separate versions...

Google sheets does have UNIQUE and FILTER operators, unique syntax looks the same but filter appears different. (You can also use @dropdown to setup easy selects (eg 'dropdown from a range'), but this doesn't filter based on the question type)

Regardless of approach, I guess you would have to allow all possible permutations of appearances for each question type as well, for questions where appearances can be combined, eg for text you could have appearance numbers or numbers thousands-sep or a select could have columns-pack or columns-pack no-buttons etc, as it's not a 'select multiple' type of validation, which would require VBA and distributing an xlsm file has a whole other set of issues.

2 Likes
4

Hello @ahblake,
my proposition was more about normal (Excel) column filters in column titles cells, e.g. in the appearance sheet.

5

I did spend some time looking into this but it didn't seem worth the compatibility issues and likely performance impact. I believe that we'd have to do it differently for Google Sheets and Excel, both of which I think are important to support natively. The document would then likely not work in Numbers, OpenOffice, etc, which are also used.

Exactly, I think that adds quite a bit of complexity!

I added those to the Google Sheet when you suggested it (and you're credited in the changelog!) but I just realized I didn't update the Excel version. It's been updated now, thanks.

2 Likes