Solved

How do I update Data Validation list?

Posted on 2013-12-03
14
381 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 250 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:coahelpdesk
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
You can edit the Formulapic
0
 
LVL 45

Expert Comment

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

Author Comment

by:coahelpdesk
Comment Utility
No
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
btw, MartinLiss, I did not find any unexpected sheets in VB.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now