locale of info_type argument of CELL function

[kristian97]

Hello!

I would like to modify CELL function that is using info_type argument to work independently of Excel or Windows locale settings.

The problem is that I am using Excel with SK locale setting:
CELL("adresa"; ...)

which will not work when opened in Excel using other locale settings, e.g. for English locale it is expected as followed:
CELL("address"; ...)

Any ideas? I am not allowed to use VBA macro to do this based on company policy.

Excel file containg this CELL function will be shared periodicaly between users with many different locale setting.

I tried this but it does not work:

CELL({"adresa";"address";"Adresse"}; ...

Regardless, it would be better to not have to type variants of argument for every locale expected.

Thank you in advanced.

 

[RoryA]

I think you're out of luck without VBA. What do you need the Cell function for? (there's an ADDRESS function)

 

[kristian97]

I think you're out of luck without VBA. What do you need the Cell function for? (there's an ADDRESS function)

I need to get values of trend function parameters, to do that I need to build range that is static and computed string concatenated together.

This table calculates trend for product sells from range C2:C15 and outputs it in range C16:C20.

	A	B	C	D
1			product
2	2006​	6​	0
3	2007​	7​	0
4	2008​	8​	38
5	2009​	9​	1
6	2010​	10​	708
7	2011​	11​	30
8	2012​	12​	112
9	2013​	13​	24
10	2014​	14​	98
11	2015​	15​	100
12	2016​	16​	143
13	2017​	17​	196
14	2018​	18​	282
15	2019​	19​	4
16	2020​	20​	98	=ROUND(EXP(1)^(C$26*$B16)*EXP(C$27);0)
17	2021​	21​	107	=ROUND(EXP(1)^(C$26*$B17)*EXP(C$27);0)
18	2022​	22​	117	=ROUND(EXP(1)^(C$26*$B18)*EXP(C$27);0)
19	2023​	23​	129	=ROUND(EXP(1)^(C$26*$B19)*EXP(C$27);0)
20	2024​	24​	141	=ROUND(EXP(1)^(C$26*$B20)*EXP(C$27);0)
21		SUM	592	=SUM(C$16:C$20)
22
23		column letter	C
24		range from first non zero value	$C$4:C$15	=CELL("adresa";INDEX(C$2:C$15;MATCH(1;INDEX(ISNUMBER(C$2:C$15)*(C$2:C$15>0);0);0)))&":"&C23&"$15"
25		range of years	$B$4:$B$15	="$B$"&ROW(INDIRECT(C24))&":$B$15"
26		n value for trend function	0,091	=INDEX(LINEST(LN(INDIRECT(C$24));INDIRECT(C$25);TRUE;FALSE);1;1)
27		m value for trend function	2,774	=INDEX(LINEST(LN(INDIRECT(C$24));INDIRECT(C$25);TRUE;FALSE);1;2)

 

[RoryA]

So:

=ADDRESS(MATCH(1;INDEX(ISNUMBER(C$2:C$15)*(C$2:C$15>0);0);0)+1;3)&":"&C23&"$15"

?

 

[kristian97]

So:

=ADDRESS(MATCH(1;INDEX(ISNUMBER(C$2:C$15)*(C$2:C$15>0);0);0)+1;3)&":"&C23&"$15"

?

Thank you very much Rory for your help, it works.

I only needed to modify it to get number of column, because I have many more product in next columns.
=ADDRESS(MATCH(1;INDEX(ISNUMBER(C$2:C$15)*(C$2:C$15>0);0);0)+1;COLUMN())&":"&C34&"$15"

 

[RoryA]

Glad to help.