Using INDIRECT in Data Validation seems broken

justme101

Board Regular
Joined
Nov 18, 2017
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I am working on a file which has 2 columns where data validation is needed. Let us assume the source FOR VALIDATION is the data given below:

Maths
English
Physics
ChemistryPE
ABAAA
BCBBC
CDCCF
DEDG
EE
F
G

This needs to be validated into two columns, let's say A1 and B1, where a user selects a in cell A1 form a drop-down (Maths, English....) he will get the relevant list in a drop-down in cell B1 (he will get B,C,D if he selects English). Now, you might be thinking this is a very simple IF condition, but this is just a sample of the original data, which is huge and has many more such lists. So, the nested IF condition I put there was greater the characters allowed in the customer validation field. So, I used LIST NAMES and named all the lists, then used =INDIRECT(A1) in the validation for cell B1. (Hope this is making sense)

Now the problem is, I get all the values of the subjects in A1, but for some reason when I select the first or last value form A1 (Maths and PE in this example), it simply DOES NOT show up any of the values in the list. All other values in between (English, Physics, Chemistry) are working fine. Hopefully I find a solution here as I am having a headache from this. Thank you.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Z51

Well-known Member
Joined
Dec 12, 2007
Messages
653
Office Version
  1. 365
  2. 2016
Hmm, seems to work for me.

Assuming A1:E1 are headers (Maths, English, etc.). Select those cells and give it a name like Subjects
Select A2:A6 and name it Maths
Select B2:B4 and name it English
Select C2:C5 and name it Physics
Select D2:D8 and name it Chemistry
Select E2:E5 and name it PE

In F1, Data > Validation > List > =Subjects
In G1, Data > Validation > List > =INDIRECT(F1)

Whichever subject I pick in F1, it then shows the correct grades in G1.
 

justme101

Board Regular
Joined
Nov 18, 2017
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Hmm, seems to work for me.

Assuming A1:E1 are headers (Maths, English, etc.). Select those cells and give it a name like Subjects
Select A2:A6 and name it Maths
Select B2:B4 and name it English
Select C2:C5 and name it Physics
Select D2:D8 and name it Chemistry
Select E2:E5 and name it PE

In F1, Data > Validation > List > =Subjects
In G1, Data > Validation > List > =INDIRECT(F1)

Whichever subject I pick in F1, it then shows the correct grades in G1.

Right, I tested this out on a new file and it worked as well. So, in the main file, I deleted all the list names and renamed them. Then used the following formula (because the name PE was in it's full form, Physical Education, and it was not letting me name the list with a space, so I put an underscore in between).

=INDIRECT(SUBSTITUTE(F1," ","_"))
 

Forum statistics

Threads
1,181,220
Messages
5,928,753
Members
436,628
Latest member
Krakenfan69

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