Data Validation

[hawke1935]

Years ago I created a sheet with validation ( see eg ) Range = code located on sheet Codes
in colum 'A' a drop down validation box, select g/l code list ( is made from gl = description)
the value returned is the value in the left column of the range.
How do I do this again, I need to use the idea but in another sheet.
Thanks in advance
Roger

[Marcol]

Hmm?

It seems to me that you want to see in the drop-down in "Itemized Expenses" Column A to show the "G/L Code" and its' "Description", but only Return the "G/L Code"

To achieve this you have Concatenated "G/L Code" & Space & "Description", in Sheet "Codes", and named the range "code". Then used this to display the "Long Description" in the drop-down.
The definition of this name is probably better being dynamic.

Name "code"
Refers to:=

Please Login or Register  to view this content.

Data Validation for "Itemized Expenses" A2 down

Allow:= List
Source:=

Please Login or Register  to view this content.

To get this to return the "G/L Code" only, you have used the VBa Change Event for Sheet "Itemized Expenses" to lookup the "Long Description" in Sheet "Codes" and return the corresponding "G/L Code", this therefore over-rides the validation list.

If your "G/L Gode" has no spaces, then this, in the Change Event, might be a little easier

Please Login or Register  to view this content.

The formula you are using in "Constructed Key Field" is perhaps tidier as

Please Login or Register  to view this content.

Hope this helps.

[hawke1935]

Thanks for the quick reply.
However the code does not work as expected. The VB code does not have any referance to the range ( code )
I've attached the actual spreadsheet I'm working with as it may shed some light on what I'm trying to do.

[jaslake]

Hi hawke1935

I don't have a real good sense of what your trying to do. Based on my understanding, this code may work for you.

Please Login or Register  to view this content.

[hawke1935]

Thanks John for the reply, really appreciate the help in the forum.
Let me explain maybe in detail.

On the validation sheet column A contains G/L Codes. Column B contains G/L Codes + the G/L description.

ON the detail sheet in column F , I have formatted for a Validation drop box to display the contents from the Validation sheet column B ( also known as range CODE )

Now, when you select a item from the drop box on the detail sheet in cell F9 say " the first item: 261 Asbestos Inspection " I want the the corresponding value in column A, of the validation sheet , in this case "261" to be placed in column F9 on the Detail sheet.

Hope I detailed this out properly.
Thanks again for the help
Roger

[Marcol]

Hmm?
Referring to your first post

Years ago I created a sheet with validation .... How do I do this again .... ?

Perhaps you should have asked a somewhat different question, for example the one you now pose?

[jaslake]

Hi Roger

I'm an OLD retired CPA...I ALMOST understand what you MIGHT want.

Alrighty, in your example, F9 will contain "261". What will E9 contain?

An even a more "pregnant" question...is this a** backward?...do you wish F9 to be populated based on what you've chosen in E9?

[hawke1935]

I think you do understand, I want to use the drop box and have it insert "261" in F9. remember that the drop box displays from a list that is a conatation of 2 columns.

The entry in E9 was the result from the VBA code I inserted in the code sheet from a previous member in the reply. It was a result that I had not considered, may or may not keep, not really neccessary .

Roger

[jaslake]

@ Marcol

Sometimes things aren't readily apparent...we, you and I and hawke1935 will get it figured out.

[jaslake]

Hi Roger

So you wish F9 to display ONLY the GL code? If true, I'll think on it but will probably suggest a different approach. OK?

[hawke1935]

Tks

Roger

[jaslake]

Hi Roger

I expect you'll need much more from this but perhaps this'll be a start

Please Login or Register  to view this content.

Select your Dropdown from Column E. Let me know how I can help.

[hawke1935]

Its another way to do it, but it works elegantly. Thank you all who helped.

Roger


Also check out the attached workbook, especially the itemized expenses, and see how the GL code works