Strange behaviour with formulas in different sheets

Topics: Developer Forum
Apr 26, 2013 at 9:27 AM

I'm doing some debugging for a problem I have regarding formulas not getting parsed correctly. Its getting there but I discovered some strange behavior when doing some testing using a formula that includes variables from a different sheet.

For example, I have the following formulas:
=IF($A3="Diåmeter";MATCH($B6;Uträkning!$C1:$C6;0)*MATCH($B4;Uträkning!$D1:$D6;0); -1)

=IF($A3="Beräkning";MATCH($C3;Variabler!$B1:$B6;0)*MATCH($C4;Variabler!$C1:$C6;0); -1)
The first one works, the second one does not. (The name "diåmeter" is intentional)
The first formula is taking values from the same sheet as the second formula and vice versa.
However, the sheet named "Variabler" is first in order, and "Uträkning" is second.
If I change the order of the sheets, and then change "setActiveSheetIndex()" accordingly, then the first one fails (resulting in #N/A) and the second one fails.
It seems like I can only collect variables from sheets having a higher indexnumber than the sheets Im working in.
Why is that?
Apr 26, 2013 at 12:56 PM
Edited Apr 26, 2013 at 12:57 PM
Neither of these formulae should work as you're using a semi-colon (;) as your function argument separator rather than a comma (,)

But index numbering of sheets isn't relevant to the calculation at all.... if it needs to retrieve data from a sheet it is always referenced by sheet name/title
Apr 26, 2013 at 2:04 PM
Edited Apr 26, 2013 at 7:58 PM
Should the the semi-colon issue be resolved by setting the LocaleSetting ?

The problem I had as related to cache, disabling it fixed the problem.