Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel validation source

Posted on 2007-03-21
4
Medium Priority
?
154 Views
Last Modified: 2010-04-30
I am using data validation and I need to set it up so that if a cell is added to the source, it will automatically add it to my list box. For example, right now the source for the validation is:
=$BA$1:$BA$28
How do I set it up so that if I input into Range("BA29 "), it will automatically update it?


0
Comment
Question by:tiehaze
[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
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:WJReid
ID: 18764432
Hi,

If you make it a dynamic range,

Insert|Name|Define, then type in the Name of the Range you want (RangeName) for example and in the formula box type

=offset(BA1,0,0,counta(BA1:BA200))
This will allow up to 200 entries.

then do the validation and set it up to =RangeName
0
 
LVL 13

Accepted Solution

by:
WJReid earned 2000 total points
ID: 18764468
Hi,

In the Data|Validation, first select List, then in the Source box type =RangeName or whatever you call the range you have used for the validation.
You could also use $ to make it absolute values, e.g.

=offset($BA$1,0,0,counta($BA$1:$BA$200))
then you can copy the validation to other cells. If you need more than 200 entries, just change the 200 in the formula to whatever you like.

Bill
0
 
LVL 45

Expert Comment

by:patrickab
ID: 18764475
You could set it up with this in the 'source box in Data Validation:

=BA:BA

That would include anything you have in column BA.

If you want to include only those cells containing something put this in the source box:

=OFFSET($D$1,0,0,COUNTA($D:$D),1)
0
 
LVL 45

Expert Comment

by:patrickab
ID: 18764492
Bill - That's a cross over... although I got the column wrong in the dynamic range - Patrick
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

618 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