data validation + offset

rich_cirillo

Board Regular
Joined
Sep 26, 2012
Messages
247
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Sample data...

Excel Workbook
ABCDEF
1Data1Data2Data3*DV List*
2RedBill100*Bud*
3GreenBob50***
4BlueBud150***
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
ABCDEF
1Data1Data2Data3*DV List*
2RedBill100*Ben*
3GreenBob50***
4BlueBud150***
5PinkBen200***
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top