Create Interactive Data Validation by Using Indirect Formula

Data Validation is one of the good option in MS Excel.

Now, we will see how to Create Interactive Data Validation in MS Excel.

Options to be used :

1. Data Validation
2. Define Name

Formulas to be used :

• INDIRECT

Steps to be followed: -


We are trying to apply the Data Validation for the below.


Which Social Networking Site you like more?


Two answers needs to be filled for the above question. The second option is based on the answer selected as one.


Follow the below steps to Create Interactive Data Validation.


1. Enter Data


Enter the below data from Cell A1 to C3

Secret Question	Site Name	Option
Which Social Networking Site You Like More?

A2 Cell has the question and the answer should be in B2 and C2.


We are allowing user to select only one of the Site Name from the Social Networking sites Facebook, Twitter, Orkut, Google+.


Enter the below data starting from Cell E2 to H4.

Facebook	Twitter	Orkut	Google_Plus
Like	Follow	Scraps	Hangouts
Share	Tweet	Communities	Circles
Tag		Recent visitors	+1 Button

Above, we have entered the master data having Site Names and Options under those sites.


2. Define Names


Defining Name for the Site Name List

• Select E2:H2
• Use short cut Ctrl + F3 (then Name Manager box appears)
• Click New (Alt + N)
• Enter Name as Social_Site
• Click OK (then defined name appears in the Name Manager box)
• Click Close

Defining Name for the Site Options

If we want to Define Name for the options under all sites, we have to do the above steps four times.

Instead, we had an option to Define Names for all the four sites at a time.

• Select E2:H4
• Ctrl + Shift + F3 (then Create Names from Selection box appears)
• Check Top row option (Alt + T) (make sure all other options are unchecked)
• Click OK

Excel has been defined three names. Those are Facebook, Twitter, Orkut and Google_Plus

3. Apply Data Validation

We are using Data Validation option in Excel in Cell B2 and C2.

• Select B2 to validate
• On the Data tab, in the Data Tools group, click Data Validation (then Data Validation box appears)
• Select Settings Tab
• Select List from the Drop down list under Allow (Alt + A)
• Enter =Social_Site in Source
• Click Ok

You can find the drop down in the Cell B2 having the names Facebook, Twitter, Orkut and Google_Plus. User should select one of the Site Name from the drop down list.

Now, we will apply Interactive Data Validation in C2

Interactive Data Validation is nothing but data will be changing, once you change the dependent cells data.

In this example, you should be having the options only Follow, Tweet if you select Twitter as Site Name. If you select Facebook, the options list should be changed to Like, Share and Tag.

• Select C2 to validate
• On the Data tab, in the Data Tools group, click Data Validation (then Data Validation box appears)
• Select Settings Tab
• Select List from the Drop down list under Allow
• Enter =Indirect (B2) in Source
• Click OK (then should be getting a Microsoft Office Excel error box)
• Click Yes

Now, see what is happening in C2. If you select Facebook in B2 then the Facebook options appearing in C2 and if you select Twitter then the Twitter options appearing in C2.