Data Validation Issue using List Option and Index/Match Formula

CAROM16

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
I have named ranges used in data validation to list corresponding information which the user can select from based on the PREVIOUS FIELD input. Consequently, I used index/match and vlookup functions to assess the previous input field and then display the correct corresponding list. HOWEVER, instead of displaying the list, it displays the NAME of the associated named range only! If I use the named range by itself (=named_range) it works fine and the correct data list is displayed. When I use the formula with either index/match or vlookup which (computes to "=named_range"), then the list shows "named_range" as the only option. I can use nested if functions which appear to work but it's crazy to have to do it that way (there are 20 options). In essence, I want Excel to recognize the computed "=named_range" for what it is - a defined list of data as opposed to recognizing it as a text field called "named_range"

I hope this isn't too nebulous. Please advise if I need to clarify with specific examples. Thank you for any light you can shine on this dilemma.

Regards - Dominick
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Can you post some sample data and your validation range formula?
Have you tried including the check in the named range definition?
I don't have an answer for you now, but will try to play with it tomorrow.
 

CAROM16

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Can you post some sample data and your validation range formula?
Have you tried including the check in the named range definition?
I don't have an answer for you now, but will try to play with it tomorrow.
Thank you so much for your response. I created a simple Xcel spread sheet which includes formulas and clearly shows the issue. However, I don't see a way to attach it - just images and the upload mini-sheet option (which I don't fully understand). Can you advise on the best way to transfer the Xcel file . Thank you
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Upload it online (e.g. Google drive or OneDrive), share it and post the link.
 

CAROM16

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Upload it online (e.g. Google drive or OneDrive), share it and post the link.
I copied the file to my Documents folder in One Drive as you suggested. Clicked on "Share" and it wanted to know who I want to share it with - displaying my contacts. Who do I click on and how do you get to read it? BTW, this is not my project, rather a very small and lucid explanation of the issue at hand. Once again, thank you for your time. (Never used One Drive before).
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I copied the file to my Documents folder in One Drive as you suggested. Clicked on "Share" and it wanted to know who I want to share it with - displaying my contacts. Who do I click on and how do you get to read it? BTW, this is not my project, rather a very small and lucid explanation of the issue at hand. Once again, thank you for your time. (Never used One Drive before).
Select the link option. In this way you don't have to specify people, just send the link to anyone you like.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

BTW. I came up with a working solution. Unfortunately I am using Office365.
At present I cannot build and test a solution for 2010.
I will post it here just for completeness. All the better if it works for you, but I suspect you may face some issues.
MrExcel Playbook 02 2021-09.xlsm
ABCDEFGHIJKL
1VALS
2LISTVALSLIST 1abcd
3aa4aa1b1c1d1
4ba2b2c2d2
5ca3b3c3d3
6dd2da4b4c4d4
7a5b5c5d5
8
9
10
11VALSNot Defined
12aa13ab
13a11b11
14bb12a12b12
15a13
16cNot Defineda14
17a15
CAROM16
Cells with Data Validation
CellAllowCriteria
B3List=$G$3:$G$6
B6List=$G$3:$G$6
B12List=$G$3:$G$6
B14List=$G$3:$G$6
B16List=$G$3:$G$6
D3List=OFFSET($I$2,1,MATCH($B3,$I$2#,0)-1,5,1)
D6List=OFFSET($I$2,1,MATCH($B6,$I$2#,0)-1,5,1)
D12List=IF($B12="a",Aval,IF($B12="b",Bval,$K$11))
D14List=IF($B14="a",Aval,IF($B14="b",Bval,$K$11))
D16List=IF($B16="a",Aval,IF($B16="b",Bval,$K$11))

I have selected a LIST as a validation Allow option. This allows to have an in-cell dropdown.
The validation formulas self-adjust when you copy paste the pair of cells in columns B & D
The yellow and orrange ranges are solution version 1.
The greens are version 2 - with Named Ranges.
I don't know why it is not shown here but there are two named ranges:
Aval - i13:i17
Bval - J13:J14
 
Last edited:

CAROM16

New Member
Joined
Aug 31, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Thank you for your time, patience, and effort - especially in light of no one else took the time to respond. Unfortunately, it appears I was unable to effectively communicate my issue/concerns regarding formulas (specifically named ranges utilizing Index/Match and Vlookup) in the list option of Data Validations. I wish you the best of luck in your endeavors.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,436
Office Version
  1. 365
Platform
  1. Windows
It looks like what you want is dependent data validation.
Try using Indirect function, like this:
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Certainty the mini-sheet in post #7 i about dependent data validation. The validation of the colored cells in column D changes according to the values of the cells in column B.
 

Forum statistics

Threads
1,143,637
Messages
5,719,961
Members
422,251
Latest member
bonebreaker100

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