Excel Dynamic USELIST with data validation

**drm1963** · 11-14-2012, 06:53 PM

I have a document, spreadsheet, I have altered so that I can do more selection instead of actually having to type data into. What I need to do is have a list "Eq Number", $AA$3:$AP$4, populate based on the item selected in "AREA", $F$3:$Q$4, these are on the "Data Entry" sheet and the items to select from are on the

I am attempting to do this based on the information listed at

Please Login or Register  to view this content.

If anyone can assist me, it will be greatly appreciated and I though I would appreciate it just getting fixed what I would appreciate even more is for the information telling me what I am doing wrong and or missing.

Also feel free to look over the file and if you where I can make improvements to the Data, (the structure has to stay as is since this is a document already in use, I am just trying to create it with less typing since time is crucial and the less time spent doing paperwork the better for us guys on the floor.

When it has been completed I will lock it so that only the areas that need input are the only ones that are being utilized and the document and structure cannot be changed by the end user.

Also feel free to check out the "Repair Time Tracking Chart" and see if anyone can assist me with making the boxes fill in better and more fluently!

If interested let me know and I can send you the document!

Heck I am just going to attach the document and thank you to all who can assist me!

I am using Excel 2007 / 2010

Thank you in advance

**rylo** · 11-15-2012, 01:16 AM

Hi

You have to actually create the named ranges that are going to be used in the list. So go to sheet LISTS, select the range(B2:B75) and give it the defined name of BODYSIDE_INNER_LH_ARG_1. Now go back to Data Entry, select R3 and insert your datavalidation, list, with the formula =INDIRECT(SUBSTITUTE(F3," ","_"))

Extend to cover all your names.

HTH

rylo

**drm1963** · 11-16-2012, 10:59 AM

Somehow I thought that the naming of the columns was to be automatic based on the values entered into the rows in the first column. This way as more items were added all you would have to do was populate the NEW column with the data.

I do not know how I go so far out with this to make it become so difficult.

rylo, I will try what you specified to see if it works for me

**drm1963** · 11-17-2012, 05:25 AM

This does work to an extent but if any data is added and or a new item to the first column and you add the column to the list you will have to add a new NAME into the NAME MANAGER before that information can be pulled and utilized inside the spreadsheet.

I am attaching another spreadsheet that I took this idea from, but again I cannot find what the difference is and I am sure it is something I am overlooking and if you will notice in the NAME MANAGER there is not a name for each individual column.

Like I said I am sure it is something I am just overlooking but for some crazy reason I just cannot see it.

Thank you again!!