Solved

How do I update Data Validation list?

Posted on 2013-12-03
14
411 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
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 46

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 46

Expert Comment

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

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 46

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 46

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 46

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Outlook Free & Paid Tools
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.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

856 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