data validation + offset

[rich_cirillo]

Hi

Is it better to use the Offset function when doing Data Validation Lists...i will add more entries into the lists so do you recommend using Offset function

How do i set up and use the Offset feature in with Data Validation

Thanks

Excel 2010 / 2013

 

[Akashwani]

Hi,

You could always convert your "List" to a "Table", then when you add new entries the "Data Validation List" will update.
Other than that, I guess you could use a range where you know that you will not have that many entries, eg $A$2:$A$1000 or $A$2:$A$5000, I'm sure you get what I mean from that.

I hope that helps.

Ak

 

[rich_cirillo]

Thanks AK

Would you have a basic example of what you mean by table

Thanks

 

[Akashwani]

Hi,

Sample data...

Excel Workbook
	A	B	C	D	E	F
1	Data1	Data2	Data3	*	DV List	*
2	Red	Bill	100	*	Bud	*
3	Green	Bob	50	*	*	*
4	Blue	Bud	150	*	*	*
5	*	*	*	*	*	*
Sheet14

Create a Data Validation List using $B$2:$B$4 as the Source, Bud is the last record in the list.
To create a Table for the whole range of cells, press alt N T click Ok

Add a new record to your table, in the following example Ben is the last record in the Data Validation List....

Excel Workbook
	A	B	C	D	E	F
1	Data1	Data2	Data3	*	DV List	*
2	Red	Bill	100	*	Ben	*
3	Green	Bob	50	*	*	*
4	Blue	Bud	150	*	*	*
5	Pink	Ben	200	*	*	*
6	*	*	*	*	*	*
Sheet14

You may want to take a look here....

Excel Magic Trick # 146 part 1: Dynamic List from Table #1 - YouTube

Or this one....

http://www.youtube.com/watch?v=OBcBlBJvlZA

I hope this helps.

Ak