This one has me tearing my hair out.
I have a category group of 32 items. The user can select a check box next to the ones s/he wants to include. I want to be able to create a dropdown list which allows the user to select one of the group of items s/he has checked. I have gotten to the point where I can create the drop down list, but it has blanks where the user did not select an item to include.
I tried using an array formula and another formula using AGGREGATE (which work outside of Modano), but neither works within Modano (which does not allow array formulas in components).
How can we remove blanks from that drop down list?
Hi Winthrop,
This is a difficult question to follow without the model itself.
Can you upload a small part of this file? Or ideally put this through the support system?
Thanks. J.
Hi Winthrop,
You should be able to do this using an OFFSET function with a Named Formula, which you can create within modules via the Build tab, Tools menu, Names sub-menu, Named Formula, as shown below:
The Named Formula dialog allows you to enter a formula which returns a range which you can then reference within your data validation list. If you include an OFFSET function within the named formula Refers to field, you can make it size dynamically, as shown below:
You just need to set up a category block which includes the non-blanks sequentially and then make the [ReferenceNonBlanksCount] argument a reference to a cell which counts the number of non-blanks in the category block cell block - i.e. using the number of rows minus COUNTBLANKS, or something similar.
After doing this, you can reference the named range in your validation list source, as shown below.
The only fiddly bit of this is that you'll need to create a category block which references your source list category block but puts the non-blanks before all the blanks, but this should not be hard to do.
I hope this helps, it's a pretty cool little trick. M.
Thank you very much for this explanation and for your patience awaiting my acknowledgement.