Create Drop-Down List Using a Formula Without Data Validation

0 votes
The data validation tool can be used to build a list. But can you accomplish the same thing using just a formula (and not VBA)?

I can make a reference to a named range in another cell (=Names) if it consists of numerous cells and is named. However, no drop-down menu offering all alternatives will be constructed; only the contents of the first of these cells will be visible.

Is it possible to do this without using VBA and without validating the data?
Nov 8, 2022 in Others by Kithuzzz
• 38,010 points
2,238 views

1 answer to this question.

0 votes

As far as I am aware, it is not possible to generate a drop-down list using a formula rather than data validation, however, the following approach might be utilized to meet your needs.

  1. A page layout like this:

enter image description here

  1. Data validation formula as following:

    =IF($A$2="List",$C$2:$C$8,$D$2:$D$8)
    

enter image description here

  1. And also alert option will be disabled ( to be able to add custom data):

enter image description here

  1. When you write "List" in the cell "A2" you will get the Column C in the drop down list and if you do not write anything in cell "A2" you will get D column in the drop down list.

enter image description here

enter image description here

answered Nov 8, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
729 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
712 views
0 votes
1 answer

Excel Drop down list using Apache POI

To set a default value, just setCellValue("first_item_value"); she ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
532 views
0 votes
1 answer

Excel data validation list

We can use nested if - if ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,420 points
370 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,237 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,424 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
697 views
0 votes
1 answer

How to create a dependent drop down list using [Apache POI]

There is nothing apache poi cannot do, ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
4,619 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
422 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP