Solved

How do I update Data Validation list?

Posted on 2013-12-03
14
426 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
[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
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 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 250 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 47

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 47

Expert Comment

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

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 47

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 47

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 47

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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