Range Naming

[ATAHER]

Hi guys

Actually i face aproplem in naming range
i want to name range by combination of numbers and word is it possible
because i have some complecated dropdown list for chart of accounts
with 5 dimentions (Cost Center)

 

[GlennUK]

I think you need to provide more information before you get a detailed answer.

 

[ATAHER]

for eample

Main ledger:

100-Assets
200-Liabilitite
300-Capital


The above will be the first drop down list , based on your selection for example if you select 100-Assets

the second drop down list will be only:

101-Cash
102-Bank
103-Account receivable
104-Fixed Assets

The third drop down list if you select for example 104-Fixed assets will be:

1041-Cars
1042-Land
1043-Building
1044-Tools

my problem is : that i cant name range by numbers for example:
104-Fixed Assets
even i am not able to name the range by words with space like :
Fixed Assets i should name it like FixedAssets

Can you help me please

My Regards

 

[GlennUK]

When you create names from headings like that Excel puts and underscore before the initial number, and changes embedded spaces and hyphens to underscore. When you INDIRECTly reference the required names in your Data Validation you have to do the same thing to the previous level. So, for example, here is a sample layout:

Excel Workbook
	A	B	C	D	E	F
1	TopLevel	100-Assets	200-Liabilitite	300-Capital	101-Cash	104-Fixed Assets
2	100-Assets	101-Cash	201-Item1	301-Cap1	1011-Cash1	1041-Cars
3	200-Liabilitite	102-Bank	202-Item2	302-Cap2	1012-Cash2	1042-Land
4	300-Capital	103-Account receivable	203-Item3	303-Cap3	*	1043-Building
5	*	104-Fixed Assets	204-Item4	304-Cap4	*	1044-Tools
6	*	*	205-Item5	305-Cap5	*	*
7	Defined names	*	206-Item6	*	*	*
8	_100_Assets	=Sheet2!$B$2:$B$5	*	*	*	*
9	_101_Cash	=Sheet2!$E$2:$E$3	*	Dropdown 1	Dropdown 2	Dropdown 3
10	_104_Fixed_Assets	=Sheet2!$F$2:$F$5	*	100-Assets	104-Fixed Assets	1043-Building
11	_200_Liabilitite	=Sheet2!$C$2:$C$7	*	*	*	*
12	_300_Capital	=Sheet2!$D$2:$D$6	*	*	*	*
13	TopLevel	=Sheet2!$A$2:$A$4	*	*	*	*
Sheet2

And the data validation for cell E10 is List, with a definition of:

=INDIRECT("_"&SUBSTITUTE($D$10,"-","_"))

and for F10 it's:

=INDIRECT("_"&SUBSTITUTE(SUBSTITUTE($E$10,"-","_")," ","_"))

I hope you get the idea.

 

[ATAHER]

Thanks alot sir,

It is work in perfect way

 

[GlennUK]

That's great!