asked on

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?

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Brian Pierce

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

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.

coahelpdesk

ASKER

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.

Martin Liss

You can edit the Formula

Martin Liss

Do you have a sheet called "Business Unit No"?

coahelpdesk

ASKER

Martin Liss

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?

coahelpdesk

ASKER

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.

coahelpdesk

ASKER

btw, MartinLiss, I did not find any unexpected sheets in VB.

Martin Liss

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.

Saqib Husain

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.

coahelpdesk

ASKER

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.

Martin Liss

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.