• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown List without Data Validation?

A

AndrewS

New Member
Could anybody help me with this, is it even possible to have non-D.V. dropdowns? I'd like to make a dropdown list without using data validation because of DV's inherent limits (not being able to override the cell, etc).

The main reason I need this is because I've found data validation to be buggy. I can use if statements sometimes, other times the same statement will be rejected with strange error messages. And sometimes after it's accepted my data validation if statement (still for dropdown lists), it won't allow me to edit it, giving the error message that there is more than one type of data validation in that cell and all must be erased before editing.

So it would be ideal if I could use dropdown menus that don't use data validation, because figuring out why it's bugging on me so badly doesn't seem to be going anywhere (as it's very non-linear and random without any reason I can yet see).
 
Hi AndrewS,

First, sorry to hear you're having trouble with Data Validation. First, there's no way to use the cell's drop down w/o having DV. Second, rather than attempting to find a work-around, I think the better route is to properly learn how DV works, and try to figure out why your current formulas aren't working.

You can certainly post examples of where your DV is going awry to this forum, and we'll be happy to help out. :)

For the limitation on adding new items, that can be over come as well. Debra has a nice tutorial on how to add items to the list, if that's what you want.
http://www.contextures.com/excel-data-validation-add-tables.html

Or, you can just uncheck this box
upload_2015-4-29_16-2-8.png
which let's user put whatever they want (but what they entered is NOT shown in future dropdowns).
 
Well I'd like to post the formulas but as I said excel won't let me see them anymore. When I try to enter the DV menu on any of the cells with the working DV, it gives the error message "the selection contains more than one type of data validation. Erase current settings and continue?"

As I said, I think it's a bug. I copied and pasted the same code into each data validation range section (copied and pasted TEXT, not a cell, so the text would not have changed between pasting, ie every variable was controlled and the same) - and after doing so it worked sometimes and didn't work other times. The times when it did work are when I get the described error message and I can't access it anymore.

The formula in the dropdown menu is in column C and looks something like this:
=IF(B16='Fund Data'!$C$57, 'Fund Data'!$D$58:$D$63, IF(B16='Fund Data'!$C$62, 'Fund Data'!$D$64:$D$73, IF(B16='Fund Data'!$C$71, 'Fund Data'!$D$74:$D$76, IF(B16='Fund Data'!$C$77, 'Fund Data'!$D$78:$D$84, IF(B16='Fund Data'!$C$85, 'Fund Data'!$D$86:$D$93)))))

It's designed to populate the dropdown menu in C based on the choices of the dropdown menu in B.

I don't think that I need more tutorials on how to do things with DV dropdowns that don't pertain to what I'm trying to do. I need to know why this error message is happening and why DV dropdown often rejects perfectly viable code..
 
You are selecting multiple cells, (you might have a merged cell) before opening the Data Validation men. The message, as it says, means that the data validation is not consistent. E.g., if A1 has a validation of
>5
and A2 has a validation of
>6

If you then select A1:A2 and go to Data Validation, XL will give you a warning, since it can't display both rules simultaneously, and if you press Ok, it will take whatever rule is in top-left cell and apply it to all cells. So, not a bug, just a warning that your DV is not consistent. Inconsistent DV can happen fairly easy when you copy/paste different cells, or when dealing with relative cell references if you're not careful.

I'm assuming that when you use the word code you're referring to the formula, and not actual VB coding.

Might be able to simplify your formula a bit, but it looks like the layout on Fund Data is not consistent. By that I mean, a match for C57 looks at D58, (one row down, 1 col over), but a match for C62 goes to D62 (two rows down, 1 col over). Any chance we can change this layout, or is it locked down? If flexible, we could do something like this:
upload_2015-4-30_11-1-50.png
where B16 has one of the values in A1:C1, and our DV formula becomes:
=INDEX($A$2:$C$5,,MATCH(B16,$A$1:$C$1,0))
 
I am dealing with merged cells, which might be why DV is acting up.

I agree that XL can produce all kinds of inconsistencies when copy and pasting XL code, which is why I mentioned that I was so careful in just copying the literal text and doing a direct paste, and not copying the cell, as well as double checking it. This is telling me that the error is likely a result of the merged cells (which means it's fixable and not a bug!) :)

The references are fairly inconsistent due to the difference in the sizes of the sections they're referencing. I've since changed the ranges to tables, so we'll see how that goes.

Unfortunately I'm not able to arrange the data in the format in your helpful image, because what I'm doing now is simply one phase of a larger matrix, where lookups will be necessary for the items selected by the dropsdowns in question, so they will need to be layed out vertically to allow alot of horizontal cells for lookup data to reference the lookup item.

I'll let you know how it goes!
 
Stillll not working.

I've unmerged all of the cells including the cells in the tables (the tables are just one column wide), and I'm using this formula as my DV dropdown list, which it's rejecting.

=IF(B16='Fund Data'!$C$57, GlobalBondsTable, IF(B16='Fund Data'!$C$62, AlternativeYieldTable, IF(B16='Fund Data'!$C$72,AbsoluteReturnTable, IF(B16='Fund Data'!$C$78, RealEstateTable, IF(B16='Fund Data'!$C$86, InfrastructureTable)))))

It's giving me the error "we found a problem with this formula. If you are not trying to write a formula" bla bla bla.

What am I doing wrong??
 
Are those names (e.g. GlobalBondsTable) Named Ranges referring to just the data portion of a table (good), or the actual name of entire table (bad)?
 
The name of the entire table.. the table only contains data though, just one column of the items. Guessing I should name a range in the table then? :p And if so, what would be the difference between simply using named ranges versus using named ranges in a table?
 
Nothing really. You either need to make a named range referring to data, or change your formula to be like
=IF(B16='Fund Data'!$C$57, GlobalBondsTable[My Col], ....
which-ever you prefer.
 
You must log in or register to reply here.
Back
Top