Excel 2007 : Data validation

[sygeorgio]

My spreadsheet has a column heading called CAUSE CODE. In this column I want to enter BF (for bush fire) or FN (for fallen out of nest). However...and here's the catch. I want my data validation to have a list so that I can see all the very numerous causes with their codes BUT only want the code displayed/returned in the column, not the actual cause.

I have looked all over for a solution...maybe Excel cant do this sort of thing? Can anyone help?

[teylyn]

Hello,

Hang on... Data validation is for one cell. You want to allow entry of some values only, in this case either BF or FN. So far, so good.

Where does the rest come in? I can't quite picture what you want to achieve. Apparently, it's more than a simple data validation list with two choices.

Can you explain a bit more? Maybe post a workbook with some sample dummy data and explain in context?

Could it be that what you want to see in the data validation drop-down looks like

BF - Cause 1
BF - Cause 2
BF - Cause 3
BF - Cause 4
BF - Cause 5
BF - Cause 6
FN - Cause 1
FN - Cause 2
FN - Cause 3
FN - Cause 4

and when the user selects, for example, FN - Cause 3, the cell will show only "FN"

Is that it?

Please clarify.

cheers

[teylyn]

See attached file, cell A2. It has a drop down data validation list with all the options listed in column L. Once you select a value, the Worksheet_Change event kicks in and truncates the entry to the first two characters of the cell.

Does that help?

cheers,

[royUK]

You could avoid VBA by using a Forms ComboBox, with the Index Formula

[sygeorgio]

thats exactly what I want! So the spreadsheet is for wildlife rescue and a rescuer doesnt know nor cares about the codes, they just know that the animal was in a bush fire. However, National Parks only want the code (thats how their database works). So the rescuer need to see that the cause is bush fire, select that cause but have code BF returned in the cell.

[sygeorgio]

OMG! that is awesome! how on earth did you do that? Are you able to give me some instruction?

[royUK]

Who are your replies to?

[sygeorgio]

Hi, my apologies. I have only got so far as to look at the Worksheet change event function (from Teylyn) but I suspect may be VBA and beyond my level of competence? It works beautifully though....going to look at the forms combo box solution now and see if I can figure that one out as well.....
In the meanwhile though, thanks everybody!

[teylyn]

Hello,

the data validation list in my example is sourced from a range of cells in the workbook. A macro monitors the cells with the data validation and will change a value every time it has been changed. The macro simply reduces the cell text to the first two letters of the cell value.

You can find the macro when you right-click the sheet tab and select "View Code".

cheers, teylyn

[sygeorgio]

HI Teylyn, Thanks. I have done that and seen the code. How do I copy and paste that code into my proper spreadsheet, in the column it belongs?

[teylyn]

Ctrl-C to copy, Ctrl-V to paste.

Right-click the sheet on which you want the code to run and select View Code. Paste into the code window. Change all references to column L to the column you want to monitor.

[sygeorgio]

Hi Teylyn, I am pleased to say that I have progressed to editing the code to the new column - yay!. My next question is (sorry...hope you don't mind) , can I have multiples of these sorts of actions, in the one spreadsheet ie. I would like to be able to use this same feature throughout the spreadsheet, for various columns?

[teylyn]

To run the code on cell A2 only, use the existing code with this line:

Please Login or Register  to view this content.

To run the code on all of column A, use

Please Login or Register  to view this content.

To run the code on columns A and C use

Please Login or Register  to view this content.

Can you see how it works?

[sygeorgio]

wow, cant believe I have done all you suggested and it works beautifully! Going to be such a help for the non-profitable organisation that I volunteer for. Thanks very much. The one thing I would have liked on the drop down lists is to be able to format the size of the font but from what I can gather from other blogs, is that this is not possible. One or two of my columns, have quite a bit of data with its code for example : age = out of nest but depend on parents = ON. So your VBA code has helped me put this in a drop down list, if user selects this option, then "ON" is returned in the column but unless I make the column width really wide, they may not be able to read the full definition....BUT this is just a little thing which would be nice to work around, but not essential. Vielen dank!

[teylyn]

Gotta support the NFPs!! Gern geschehen!

(translate.google.com)