Excel- Create Dynamic range

By Ajeet Kumar 

EXCEL: CREATING DYNAMIC LIST

GIVEN: Suppose you have a list in column A of Sheet1 of a workbook. The list can grow or shrink as per need.

OBJECTIVE: You want to load all these data in a list. To achieve this objective, we can do the following steps:

STEPS:

1. Choose a cell on any sheet of the workbook.
2. Click Data > Data Validation > Data Validation…
3. The Data Validation dialog box appears.
4. In Validation Criteria, select List
5. Write the following formula in the Source text box and click OK.

When you add or remove data from the list, the list will get updated.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)