Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Data Validation Problem

0

Hi,

Please help  if possible.

Background: The file contains 2 sheets, one sheet to enter variables (eg fabric type, garment type etc) and the second sheet is to creat garment "recipes" using the data in the LIST sheet. The drop down lists are located in the PATTERNS sheet and refer to data in lists in the LIST sheet by means of data validation. 

I don't know how I did it, but my drop down list, if located in Row 3, only shows list items 1 to 3 ; Row 4 shows list items 1 - 4, and so on. What have I done wrong and how to fix? I have tried re-entering into a new sheet but no luck. An additional problem here is that the lower the row in the PATTERNS sheet, the lower the default position within the drop down list itself. Hard to explain but if you click on each cell in Column A going downwards, you will see how the appearance of the list changes in each cell.

(There are 2 small macros in the workbook but I don't think you would need to enable them to answer my query. They are basically just to sort the data in the lists so I can use LOOKUP later.)

Thanks in advance!

UPDATE: Please note that the lower end of the list range is not locked as I need to add items to the list and still have them appear on the drop down. I am reasonably certain that this isn't the problem. But perhaps I am misunderstanding something.

exceldata-validation
MS Office Version: 365
posted by: Srixxon (rep: 2) on Wed Jul 20, 2016 at 2:38 pm
Answer
Discuss

Discussion

I updated my answer below.
don (rep: 1989) Jul 21, '16 at 12:50 pm
Add to Discussion

Answers

0
Selected Answer

The ranges that you are using in your data validation are not completely fixed so when you copy the validation down the sheet the range changes and includes more values.  Example:

Garment should be =LIST!$A$5:$A$11 not =LIST!$A$5:$A11

posted by: MAQ (rep: 15) on Wed Jul 20, 2016 at 3:05 pm
Discuss

Discussion

Hi, thanks for your answer. I deliberately didn't lock the last cell so that I could keep adding values to the bottom of the list. In any case, I locked it, checked whether I could still add new items (I couldn't) and so I unlocked it again. I also don't think It's a copy/paste issue as I also tried typing in the validation fresh at various areas on the sheet with the same result.
At the risk of sounding disrespectful I think the problem is something else...
Srixxon (rep: 2) Jul 20, '16 at 3:27 pm
Perhaps I am misunderstanding.  If you go to the patterns sheet and put your cell pointer in cell A2 and then look at Name Manager, you will see that your Garment range is $A$5:$A6, so by definition can only return 2 results.  Move your cell pointer to A7 and now look in Name Manager and your range is now $A$5:$A11 so will bring back 7 results. 
MAQ (rep: 15) Jul 20, '16 at 3:39 pm
Ah, I see. I have edited the list in Name Manager now, and it seems to be working. The list is now
=LIST!$A$5:$A10
which is still not "locked" at the end but is showing the full range of items in the list from top to bottom. And is working in all the cells below as well

Thanks for your help.
Srixxon (rep: 2) Jul 20, '16 at 5:23 pm
Add to Discussion
0

Ah, I see. I have edited the list in Name Manager now, and it seems to be working. The list is now
=LIST!$A$5:$A10
which is still not "locked" at the end but is showing the full range of items in the list from top to bottom. And is working in all the cells below as well

posted by: Srixxon (rep: 2) on Wed Jul 20, 2016 at 5:25 pm
Discuss

Discussion

Challenge is that when you add items to rows 11, 12 and so on, they still will not show up on the dropdown for the cell that contains the range above.  IMO your best bet is to have a fixed range long enough to accomodate your anticipated list.  Or when adding additional items, inserting a cell in the existing range that will expand the range even though it's fixed. 
MAQ (rep: 15) Jul 20, '16 at 5:51 pm
I have done so, thanks. Working better.
Srixxon (rep: 2) Jul 26, '16 at 10:04 am
Add to Discussion
0

Try this formula:

Updated formula:

=OFFSET(LIST!$A$5,0,0,COUNTA(LIST!$A$5:$A$1000))

Put it in for the Garment name and everything should work and update well.

posted by: don (rep: 1989) on Wed Jul 20, 2016 at 6:00 pm
Discuss
0

Don, that's neat but a few questions please..

1) the "minus 1" seems to knock an item off the list (or am I missing something)?

2) shouldn't the CountA portion be from $A5 not $A15 (or it wouldn't work if the list only went to $A8 initially)? So...

=OFFSET(List!$A$5,0,0,COUNTA(List!$A$5:$A$1010))

3) It's less elegant and perhaps wasteful but List!$A$5:$A$1010 seems produce the same drop-down list from validation, provided "Ignore Blanks" is set in the validation rules). Is there a downside to that?

John

posted by: John_R (rep: 9) on Thu Jul 21, 2016 at 3:48 am
Discuss

Discussion

You were right! I copied the wrong formula, sorry. I updated my Answer. (Also, next time comment on my Answer or I won't know that anything directed at me was said.)

3 doesn't work though because "Ignore blank" doesn't work that way - it's kind of annoying.
don (rep: 1989) Jul 21, '16 at 12:49 pm
Thanks Don, hopefully this reply is in the right place (I'm not a regular user of forums). 

My suggestion 3 seems erratic; did work (I just recreated) but then the data validation dropdown showed all blanks. Strange butdoesn't work weell enough so I know to ignore that now- thanks
John_R (rep: 9) Jul 25, '16 at 4:04 am
No worries. I know most of us aren't used to this kind of Q&A format and it will just take a little time, that's all.
don (rep: 1989) Jul 25, '16 at 11:40 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login