How do I update Data Validation list?

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?
coahelpdeskAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You should be able to find it in

Formulas > Name Manager

from where you can edit the source.
0
 
KCTSConnect With a Mentor Commented:
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
 
Martin LissOlder than dirtCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
coahelpdeskAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
You can edit the Formulapic
0
 
Martin LissOlder than dirtCommented:
Do you have a sheet called "Business Unit No"?
0
 
coahelpdeskAuthor Commented:
No
0
 
Martin LissOlder than dirtCommented:
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
 
coahelpdeskAuthor Commented:
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
 
coahelpdeskAuthor Commented:
btw, MartinLiss, I did not find any unexpected sheets in VB.
0
 
Martin LissOlder than dirtCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
coahelpdeskAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.