dropdown list

**11help11** · 02-09-2016, 09:10 AM

Hi guys!

I'm trying to use a data validation (dropdown list) in cells that refer to multiple rows or columns. Is it possible ?

as an example:

A1 -> data validation list

A10:E10 -> first row
A11:E11 -> second row
A12:E12 -> Third row

Result: drop down list in A1 with A10,B10,C10,....,A12,B12,C12,D12,E12.

Note: I tried to select the range A10:E12 but i got an error. I also tried with a name manager that refer to range A10:E12 but still need to be only one row.

thanks

**FDibbins** · 02-09-2016, 09:28 AM

A DD needs a single column to work from. Try putting each DD's info in the same column

**AlKey** · 02-09-2016, 09:35 AM

Data Validation list in C2:C4

Select range when create your validation list.

Use INDIRECT function for your formula

example could be like SUM function:

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E
1	A10:E10		List
2	105		A10:E10
3			A11:E11
4			A12:E12
5
6
7
8
9
10	25	23	45		12
11	13	12	0		60
12		12	1

**Tinbendr** · 02-09-2016, 09:51 AM

A VBA solution. Copy to Sheet's code page.

Please Login or Register  to view this content.

**11help11** · 02-09-2016, 10:10 AM

thanks for you VBA solution, but as i'm not very good in VBA what i'm I supposed to change if my data validation is on the sheet1 and my data (A10:E12) on sheet2.

**Tinbendr** · 02-09-2016, 10:16 AM

Copy the code to sheet2 code page and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The validation list will update anytime you change a value in the sheet 2 range.

Here is a slightly different version. In this version , you can pick out the data range a little easier.

Please Login or Register  to view this content.

**11help11** · 02-09-2016, 10:17 AM

ELKY!

Book1.xlsxIn my case, this is not a sum, it's more like a data validation with text.

Maybe I can use =INDIRECT("name_manager").

but i'm not able to create a name manager with only one row. there is a formula to create a row with a matrix.