Problem with the Source in the Dropdown list Validation

Hi experts:

I've a list of data. If I want to delete a record, I select it from a dropdown list and then using macro to delete it.
However, I discovered that if I delete the first record of the list, the dropdown list will no longer function when I want to delete another record.
This will not happen if I delete other records from the list other than the first one!

I also noticed that there is a change to the source of the dropdown validation:
from =Offset($B$6,0,0,CountA($B:$B)-1,1) to
=Offset(#REF!,0,0,CountA($B:$B)-1,1).

I could not figure out what causes this change whenever the first record is deleted.

I hope some Excel experts can help me solve this problem. I have attached a sample Excel file having this problem.

Thank you.
Chan K.S.

Try changing the validation formula to:

Formula:

Please Login or Register  to view this content.

BSB

You can do a couple of things!
1. Have row 6 blank - you could even hide the row. You will get a blank at the top of your delete list, but that is probably not an issue since you are using a dynamic formula to create your list.
2. Change your OFFSET formula to start at B5. This way you would have Name at the top of your list.
3. Same as 1 above, but have something like "Delete choice below>>" in the name field. HIDE the row.

See what you think.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

2. Change your OFFSET formula to start at B5. This way you would have Name at the top of your list.

Change the second parameter to 1 (rather than 0), as in post #2, and you won't have "Name" in the list

BSB

Thank you, both BSB and David.
I changed the it to =Offset($B$5,1,0,CountA($B:$B)-1,1) and now it's working fine.

Chan K.S.

Happy to help. Thanks for the rep

BSB

Just saw BSB's suggestion - MUCH BETTER idea!

It is described beautifully. It really works for me.