• 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.

Data validation from a combined list

scecchin

scecchin

New Member
Hi


Here is what I am trying to do. I have a reference table. The table has three columns; "Symbol", "Security" and "Description". The value in "Description" is "Symbol" and "Security" combined. Here is an example:


Symbol Security Description

AAPL Apple AAPL-Apple

BBRY Blackberry BBRY-Blackberry


In a cell outside of this table, I want to enter a data validation list where the list presented is the Description, (and I am fine with that part), but when I select one item from the list, only the symbol is entered in the cell. So in other words, if I select AAPL-Apple from the list, the cell value is AAPL. (Oh, if necessary, I can obviously move the columns around.)


Thanks

Sergio
 
Hi, Sergio!

How are you performing the data validation? With the Data tab, Data Tools group, Data Validation icon, with a forms control or with an ActiveX control?

Regards!
 
Hi SirJB7


I first created a table, then created a named range (Defined names > Name Manager) that refers to the table column. Then, using the data validation icon, I choose list and enter the name I just created. So for example, the table name is << tblStock >>, and the name is << lstStock >> which refers to << =tblStock[Description] >>


The data validation is List and the source is << =lstStock >>


I am not using any code or controls.


Thanks
 
Hi, Sergio!


I can't replicate your error, give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Data%20validation%20from%20a%20combined%20list%20%28for%20Sergio%20at%20chandoo.org%29.xlsx


If it doesn't help please upload your sample file.


Regards!
 
Hi, Sergio!

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

For uploading check this in particular (but don't skip all the others, that'd be cheating!):

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
Hi SirJB7


I did as you suggested, (and I did not cheat!) so if I executed correctly, the link should take you to my sample file.


Thanks

Sergio


https://www.dropbox.com/s/65l34c2p8hsoqua/Chandoo_Validation.xlsx?v=0mcnc
 
Hi, Sergio!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Data%20validation%20from%20a%20combined%20list%20-%20Chandoo_Validation%20%28for%20Sergio%20at%20chandoo.org%29.xlsx


The update to your suggestion requires that no Symbol value contains the separator string used for Description " - ", as the formula for green ticker is:

=IZQUIERDA(F9;HALLAR(" - ";F9)-1) -----> in english: =LEFT(F9,SEARCH(" - ",F9)-1)

And the formula for retrieving the Security value is:

=BUSCARV(F14;tblStocks;2;FALSO) -----> in english: =VLOOKUP(F14,tblStocks,2,FALSE)


Despite of the fact that I don't know where, how and why are you performing this strange data validation (strange because you have a table, a cell with data validation against it retrieving a field (Description), then you want another field (Symbol) to be displayed and used for retrieving another field (Security)... as I wrote yesterday at other topic, "It's weirder than scratching your left ear with your right hand going under the left oxter.".


Regards!
 
On the presumption that you have a left arm, then perhaps... otherwise it is a way to stretch back muscles ;-)


This is an attempt at creating an investment transaction file to report from. The table of securities is to have a validated list to select the security for which I want to record a transaction. Since I hold multiple securities, many of which pay regular dividends that also have a dividend reinvestment plan, there are be a fair number of transactions. The consistency of using the table allows for any reporting, if also by pivot table, to be correct and not subject to typos. Since many ticker symbols can be somewhat cryptic, having the name with the symbol helps for clarity.


For those reasons, and despite the fact that data validation is very useful, it does lack some robustness.


Regards

Sergio
 
Hi, Sergio!


Luckily I have two arms and more luckily indeed I only one left. Not at any place, BTW.


Ok, I think I've got it. And my point wasn't exactly on finance & investments but on data, tables, databases and these stuffs. Why?


There are best practices applicable to DB, which are primarily a collection of tables, plus other things as relations, queries, reports, ..., tables like those of Excel: rows, columns, headers, uniformity and consistency of data in same column (field).


Time ago there was a man called Edgar F. Codd (http://en.wikipedia.org/wiki/Codd's_12_rules) which established certain criteria that might be used when creating DB and tables, the famous Codd's 12 rules.


Jointly with others but mainly this guy is responsible for something specifically about tables known as normal forms or data normalization (http://en.wikipedia.org/wiki/Database_normalization) that talks about whe way to arrange data in tables and in which normal form it should have (1st, 2nd, ...).


Technically there are primary keys, foreign keys, attributes, but for not boring you these are a few tips, regarding your Symbol-Security-Description table:

a) Each table must have a unique and unambiguous field or group of fields (ID)

b) All the other fields should be attributes (non related fields) or foreign keys (keys or IDs for other tables)

c) From b) follows that with the ID field you can access all the other fields and that no field (outside the ID) is needed to access other fields from the table


So... you have Symbol-Security-Description. Which is the PK? Symbol? Security and Description might be retrieved with Symbol, so attributes. Now the implementation, Symbol is too cryptic for being handled by users? Ok, use Description (which in fact is infringing many laws as it's a field composed by other fields, but it's acceptable in the user interface). But do so in such a way where it's clear for the user and you don't have to work redundantly: if you use a simple data validation with a ticker showing Description, you wouldn't need to retrieve Symbol for thereupon use Symbol for retrieve Security, as Security could have been the field of the user interface and the selection process would -as it does- have retrieved the Symbol field.


Hope I haven't confused you... more.


Just advise if any issue or further assistance needed.


Regards!
 
You must log in or register to reply here.
Back
Top