locale of info_type argument of CELL function

kristian97

New Member
Joined
Nov 12, 2019
Messages
5
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I think you're out of luck without VBA. What do you need the Cell function for? (there's an ADDRESS function)
 

kristian97

New Member
Joined
Nov 12, 2019
Messages
5
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.

ABCD
1product
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)
21SUM592=SUM(C$16:C$20)
22
23column letterC
24range 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"
25range of years$B$4:$B$15="$B$"&ROW(INDIRECT(C24))&":$B$15"
26n value for trend function0,091=INDEX(LINEST(LN(INDIRECT(C$24));INDIRECT(C$25);TRUE;FALSE);1;1)
27m value for trend function2,774=INDEX(LINEST(LN(INDIRECT(C$24));INDIRECT(C$25);TRUE;FALSE);1;2)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
So:

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

?
 

kristian97

New Member
Joined
Nov 12, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,363
Messages
5,444,023
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top