Log In
Register
Home
Active Member
Dashboards
Power Query
Power Pivot
Power BI
August 19, 2022
Offline
Hi,
I need a cell to reflect certain text "E-Learning" IF a reference cell equals to specific word "LinkedIn" otherwise to allow a dropdown list.
I've tryied to put the formula in the source field in the Validation list screen
=IF(E12="LinkedIn","E-Learning","Dropdown!$A$1:$A$8")
BUT I got the error message "The list source must be a delimited list, or a reference to single row or column
See Answer
Trusted Members
February 13, 2021
Offline
I can't manipulate your data as it is in read-only mode.
Data Validation can only take your list of cells for your drop-down, but you CAN manipulate your drop-down. If E12=LinkedIn the only option in the drop-down is E-Learning. So Maybe your code in 'Dropdown'!A1 would be
=IF('Sheet1'!E12="LinkedIn","E-Learning",first drop-down option)
A2 would be
=IF('Sheet1'!E12="LinkedIn","",second drop-down option)
and so forth.
Seems labor intensive but the other alternative (I don't know if you can do it in Google Sheets which is why it wasn't my first option) is to create a named range that if LinkedIn is entered the count is one and only shows E-Learning, otherwise will expand to the entire list. I hope I am making sense and my ramblings have helped inspire your creativity. If not, creat a version I can manipulate and I can illustrate my idea, or maybe someone else has a better one.
Edit to add an example.
July 16, 2010
Offline
Hi Ramez,
Note: this is an Excel forum, so these solutions are for Excel. There is no guarantee they will work in Sheets.
The data validation list cannot take a formula as a list item, as you've found. However, as long as the item returned by the formula is present in the data validation list, you can insert a formula in the cell that also contains data validation as long as the formula returns an item in the list or blank e.g. in cell C6: =IF(D6="LinkedIn","E-Learning","")
Then if the formula evaluates to false it will return a blank, and the user can then select from the data validation list.
Note: once they choose an item from the data validation list, the formula is replaced, so this is a one-time use solution. There is no other way to accomplish what you want because you cannot have a formula in a data validation list.
Mynda
Answers Post
1 Guest(s)
