•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Drop down list from discontinuous cells not working
Our take
Creating dropdown lists in Excel from non-contiguous cells can be a challenge, especially when you're working with data on a separate sheet that you can't modify. The error message stating, "You may not use reference operators for data validation criteria," can be frustrating, particularly when you know the logic behind your selections. While Excel requires contiguous ranges for data validation, there are workarounds to achieve your goal. Let’s explore some innovative solutions to help you create effective dropdown lists without the hassle.
I have to make a bunch of drop downs referencing a seperate sheet that I cant really edit. some of the lists need to reference cells that arent all contiguous.
For instance data validation list on =Sheet1!$A$1,Sheet1$C$1, Sheet!$D$1 yields a
"You may not use reference operators for data validation critera"
But leaving B1 in there works. i.e. =Sheet1!$A$1:Sheet!$D$1 works.
Any way around this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#Excel compatibility#Excel alternatives#drop downs#data validation#Excel#separate sheet#contiguous cells