Forum Discussion
Survey Ranking Validation
mathetesThank you for your reply. Yes, you are correct - The final question would have only 1 choice (really not a choice) but only remaining option.
This was fun. I added a brief explanation. The solution makes use of the FILTER function, one of a small set of new Dynamic Array functions.
- DataGeek2020May 19, 2020Copper Contributor
mathetesReceived the #NAME reference error and the following formula is all that appears in I3
=_xlfn.FORMULATEXT(I3)
Once I click on the enable macro everything displayed in the =ifERROR formula disappears
- mathetesMay 19, 2020Gold Contributor
There is no macro. I just loaded the same file I posted for you. the formula below is the only thing in cell I3.
=IFERROR(FILTER($G$3:$G$7,($G$3:$G$7<>$D$3)*($G$3:$G$7<>$D$4)*($G$3:$G$7<>$D$5)*($G$3:$G$7<>$D$6)*($G$3:$G$7<>$D$7)),"") Now, it may be that you don't have a new enough version of Excel installed. Or are you doing it on the web? The FILTER function is relatively new and may not be available everywhere.
But let's try it again, just to make sure that you didn't somehow get a corrupted version of the file.
- DataGeek2020May 20, 2020Copper Contributor
mathetesGood Morning & Thank You - We were unable to get this to work on our end. We are using Office 2010, not sure if that makes a difference. Or perhaps we were suppose to make some modifications on our end to test this for D4. Nonetheless thank you very much - you are so kind to spend your time on this. Blessing to you.
- DataGeek2020May 19, 2020Copper Contributor
mathetes- Thank you sir I will pass this on to the department. Have a great evening and be safe.