Dynamic Data Validation

[smitpau]

Hi,

I'm trying to create a dynamic data validation list so it essentially works like a =Cell & "Drop Down List" formula.

Basically I'd like to put a currency symbol before the Units (blank, '000, m) which is a referenced table, it's sticking the currency at the front of the dropdown list that's the tricky part, the Excel table below gives you an idea of how it would ideally work.

Hope that's clear.

Thanks for reading, any suggestions welcome.

Book2
	E	F	G
4	Units
5	£	$	€
6	£'000	$'000	€'000
7	£m	$m	€m
8
9	P&L 1
10	£	£
11	Unit Dropdown ->	£'000
12		£m
13	P&L 2
14	$	$
15	Unit Dropdown ->	$'000
16		$m
17	P&L 3
18	€	€
19	Unit Dropdown	€'000
20		€m
Sheet1

Cell Formulas
Range	Formula
E6:G6,F19,F15,F11	E6	=E5&"'000"
E7:G7,F20,F16,F12	E7	=E5&"m"

 

[Peter_SSs]

I am not sure that I have understood correctly but is this what you mean?

Make the table into a 'formal' table (Insert ribbon tab -> Table)
In F11 I have entered the Data Validation as shown below.

smitpau 2020-05-05 1.xlsm
	E	F	G
5	£	$	€
6	£'000	$'000	€'000
7	£m	$m	€m
8
9
10		£
11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F11	List	=INDIRECT(SUBSTITUTE("Table1[#]","#",F10))

If I change F10 to $ then

 

[smitpau]

That is exactly what I'm after thanks for the quick response.

It would also have the currency symbol as the first item in the drop-down.

For some reason when I try to use the indirect formula as a list it says "the list source must be a delimited list, or a reference to a single row or column" I'll keep trying though,

Thanks.

 

[smitpau]

Sorry I forgot to put the currency in F10, now it says the Source currently evaluates to an error, so a drop down icon is shown but no options are visible.

 

[Peter_SSs]

It would also have the currency symbol as the first item in the drop-down.

Like this?

smitpau 2020-05-05 1.xlsm
	E	F	G
5	£	$	€
6	£'000	$'000	€'000
7	£m	$m	€m
8
9
10		€
11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
F11	List	=INDIRECT(SUBSTITUTE("Table1[[#All],[%]]","%",F10))

 

[Peter_SSs]

now it says the Source currently evaluates to an error,

Do you have a formal table as described earlier?

Have you checked the table name in the DV formula is the same as the actual table name? Check the table name in Formulas ribbon tab -> Name manager

 

[smitpau]

Ah right great thanks that's it, that's all working now, I didn't tick the table has headers box so it inserted another row at the top.

Much appreciated this is very useful, will have to commit it to memory.

 

[Peter_SSs]

Cheers - glad it is sorted now.

 

[smitpau]

Sorry one final thing as the Currency symbols may change is it possible to use a table like below and start with the second row for the formula (where the currencies cell reference to elsewhere).

Returns Model Draft 5.5.20 .xlsm
	BM	BN	BO
2	Currency 1	Currency 2	Currency 3
3	£	$	€
4	£'000	$'000	€'000
5	£m	$m	€m
Settings

Cell Formulas
Range	Formula
BM3	BM3	='FX Rates'!C6
BN3	BN3	='FX Rates'!C7
BO3	BO3	='FX Rates'!C8
BM4:BO4	BM4	=BM3&"'000"
BM5:BO5	BM5	=BM3&"m"

 

[Peter_SSs]

Is this what you mean?

smitpau 2020-05-05 1.xlsm
	BM	BN	BO
2	Currency 1	Currency 2	Currency 3
3	£	$	€
4	£'000	$'000	€'000
5	£m	$m	€m
6
7		€
8
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
BN8	List	=INDEX($BM$3:$BO$5,0,MATCH($BN$7,$BM$3:$BO$3,0))