An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Course Of The Month
4 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Update Values in Excel Data Validation
Posted on 2011-03-09
Hello everyone
I have a problem with the data validation with lists in Excel.
Please have a look at my sample spreadsheet and my code.
The workbook has two sheets: name and customer.
I defined a name (called name) in name and I used data validation with lists in the sheet 'customer'. I set the data validation to accept data from name.only. So far, so good.
It can happen that I want to change the name of a customer, or whathever is in a name. This is, after all, only an example. Unfortunately, once I've changed the value, it is not automatically updated in the the lists. I have to go through each cell, or in this example: row, that contains data validation/list and change the value manually by selecting it from the pulldown menu. That's ok if I only have a few records, but it will be cumbersome once the spreadsheet is populated with a lot of data.
I decided to write a VBA code which does this job, and it works. I put the code into the worksheet name. But there is a bug: Every time I add a new name in name, the new value is added to the first empty row and each row till number 65536. Annoying.
Any ideas what I'm doing wrong? How would you fix this?
Thanks a lot
Massimo
Name.png
dropdown.jpg
Adding-a-new-name1.jpg
Adding-a-new-name2.jpg
I have a problem with the data validation with lists in Excel.
Please have a look at my sample spreadsheet and my code.
The workbook has two sheets: name and customer.
I defined a name (called name) in name and I used data validation with lists in the sheet 'customer'. I set the data validation to accept data from name.only. So far, so good.
It can happen that I want to change the name of a customer, or whathever is in a name. This is, after all, only an example. Unfortunately, once I've changed the value, it is not automatically updated in the the lists. I have to go through each cell, or in this example: row, that contains data validation/list and change the value manually by selecting it from the pulldown menu. That's ok if I only have a few records, but it will be cumbersome once the spreadsheet is populated with a lot of data.
I decided to write a VBA code which does this job, and it works. I put the code into the worksheet name. But there is a bug: Every time I add a new name in name, the new value is added to the first empty row and each row till number 65536. Annoying.
Any ideas what I'm doing wrong? How would you fix this?
Thanks a lot
Massimo
Option Explicit
Dim strName As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Name")) Is Nothing Then
If Target.Count = 1 Then strName = Target
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngZelle As Range
Dim strStart As String
Dim strNeu As String
If Not Intersect(Target, Range("Name")) Is Nothing Then
If Target.Count = 1 Then
strNeu = Target
With Worksheets("Customer").Columns(1)
Set rngZelle = .Find(strName, lookat:=xlWhole)
If Not rngZelle Is Nothing Then
strStart = rngZelle.Address
Do
.Cells(rngZelle.Row, 1) = strNeu
Set rngZelle = .FindNext(rngZelle)
If rngZelle Is Nothing Then Exit Do
Loop While Not rngZelle Is Nothing And rngZelle.Address <> strStart
End If
End With
End If
End If
End Sub
Name.png
dropdown.jpg
Adding-a-new-name1.jpg
Adding-a-new-name2.jpg
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
4 Comments
Just stepping back one.
If you are adding to a named range, you can have it automatically extend by inserting a cell within the range. however, if you insert at the end, it doesn't seem to recognize that it's part of the range so I usually insert just above the end then move the last item up and add me new item (or always leave the last cell in the named range blank.)
For example, named range "name"="A1:A3":
A1="item1"
A2="item2"
A3="item3"
If you simply add something to A4, it's not included in the range. however, if you insert at A3, you get this (just with the insert and shift cells down):
"name"="A1:A4":
A1="item1"
A2="item2"
A3=""
A4="item3"
Now you copy A4 to A3 and put your new info into A4. Your name range is now updated without changing anything else.
--
similarly if you start with "name"="A1:A4":
A1="item1"
A2="item2"
A3="item3"
A4=""
then go to A4 and insert cell down and you get:
"name="A1:A5"
A1="item1"
A2="item2"
A3="item3"
A4=""
A5=""
Now it's even easier since you just put your new value into A4. The only issue is that you have a blank entry at the end of your named range, but I think you can tell it to ignore empty cells when you use this in a data validation list.
If you are adding to a named range, you can have it automatically extend by inserting a cell within the range. however, if you insert at the end, it doesn't seem to recognize that it's part of the range so I usually insert just above the end then move the last item up and add me new item (or always leave the last cell in the named range blank.)
For example, named range "name"="A1:A3":
A1="item1"
A2="item2"
A3="item3"
If you simply add something to A4, it's not included in the range. however, if you insert at A3, you get this (just with the insert and shift cells down):
"name"="A1:A4":
A1="item1"
A2="item2"
A3=""
A4="item3"
Now you copy A4 to A3 and put your new info into A4. Your name range is now updated without changing anything else.
--
similarly if you start with "name"="A1:A4":
A1="item1"
A2="item2"
A3="item3"
A4=""
then go to A4 and insert cell down and you get:
"name="A1:A5"
A1="item1"
A2="item2"
A3="item3"
A4=""
A5=""
Now it's even easier since you just put your new value into A4. The only issue is that you have a blank entry at the end of your named range, but I think you can tell it to ignore empty cells when you use this in a data validation list.
What if you change code as per below. Also more efficient to use a dynamic named range: define "name" as =OFFSET(Name!$A$1,1,0,COUN
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngZelle As Range
Dim strStart As String
Dim strNeu As String
If Not Intersect(Target, Range("Name")) Is Nothing Then
If Target.Count = 1 Then
strNeu = Target
With Worksheets("Customer").Columns(1)
Set rngZelle = .Find(strName, lookat:=xlWhole)
If Not rngZelle Is Nothing Then
Do
.Cells(rngZelle.Row, 1) = strNeu
Set rngZelle = .FindNext(Target)
Loop While Not rngZelle Is Nothing
End If
End With
End If
End If
End Sub
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month4 days, 10 hours left to enroll
687 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.