?
Solved

How do I update Data Validation list?

Posted on 2013-12-03
14
Medium Priority
?
441 Views
Last Modified: 2013-12-03
I have a couple of Excel 2007 documents, used company-wide, that use Data Validation drop-down lists.  Those lists need to be updated with additional department names/numbers and the person who created them is no longer here.  I can see the name of the source (=DeptNumber) but cannot find it buried within the workbook... how can I update the list with new department numbers?
0
Comment
Question by:coahelpdesk
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 750 total points
ID: 39693240
You should be able to find it in

Formulas > Name Manager

from where you can edit the source.
0
 
LVL 70

Assisted Solution

by:KCTS
KCTS earned 750 total points
ID: 39693248
In the cell indicator (the one that shows the cell reference under the  toolbar click on the dropdown and select the name - Excel will then go to the named range.

Add the new option to the end of the list

Use the names manager (Under the formula option), to re-define the name to include the new option.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693257
And if you'd like to use a formula that's dynamic, take a look at this.

Range has no heading
=OFFSET('Sheet Name'!$A$1,0,0,COUNTA('Sheet Name'!$A:$A),1)

Range has a heading
=OFFSET('Sheet Name'!$A$2,0,0,COUNTA('Sheet Name'!$A:$A)-1,1)
(the A$2 is the first cell in the range)

Legend (for 'Range has a heading'
      •      'Sheet Name'!$A$2 - The referenced cell.
      •      0 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
      •      0 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
      •      COUNTA('Sheet Name'!$A:$A)-1- (Optional.) Indicates how many rows of data to return. This number must be a positive number.
      •      1  - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

=OFFSET(C2,2,-1,1,1)
      •      C2 - The referenced cell.
      •      2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
      •      -1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
      •      1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
      •      1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:coahelpdesk
ID: 39693345
OK, I found the name of the source (='Business Unit No'!$A$3:$A$112), but not the source itself... and I'm not finding any hidden worksheets... And clicking "Edit" within the Name Manager just edits the name, not the source.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693358
You can edit the Formulapic
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693364
Do you have a sheet called "Business Unit No"?
0
 

Author Comment

by:coahelpdesk
ID: 39693371
No
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693379
When you said "I found the name of the source", where did you find it?

Also go to Visual Basic, do you see any unexpected sheets there?
0
 

Author Comment

by:coahelpdesk
ID: 39693380
OH MY GOSH - never mind, I found it... I was going about "unhiding" the sheet all wrong.
Thank you for your help with Name Manager, ssaqibh and KCTS.
0
 

Author Comment

by:coahelpdesk
ID: 39693385
btw, MartinLiss, I did not find any unexpected sheets in VB.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693413
I was expecting you to find "Business Unit No" even though you didn't think it was there. And it's no big deal but I was expecting a small piece of the points.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39693432
Also, for your information on future questions, the normal grading guidelines (in my words) is

Grade A - If the answer fully addresses your problem (based on the amount of information provided)

Grade B - If the answer requires you to do some research and effort to arrive at a solution

Grade C - If the answer is only a hint and you have to do all the work to reach your goal

Also to remember is that "Cannot be done", although very depressing for the asker, is a perfectly good answer and deserves full marks with a grade A. It does not cost you anything to give a grade A.
0
 

Author Comment

by:coahelpdesk
ID: 39693441
I'm sorry MartinLiss, I appreciate you contributing but I'd already been looking for the hidden sheet before I ever asked the question.  I continued to work on it and try different things to unhide the sheet as comments were posted, then I found the different approach by rewording my question in the Excel help offered with the application... I can honestly say that only the other two comments truly helped me find the resolution.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 39693471
That's fine, no hard feelings. You may want to change the formula as in my example so that you don't have to always change it if you add or subtract data.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question