We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Calc formulas that reference sheets within the same workbook are not properly converted when saving ods to xls or xlsx [closed]

asked 2017-07-30 16:13:17 +0200

ptah gravatar image

updated 2020-10-11 17:37:56 +0200

Alex Kemp gravatar image

Version: 5.1.6.2 Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2 CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; Locale: en-US (en_US.UTF-8); Calc: group

The following formula is a simple array lookup and linear interpolation where the look up value is in the same sheet as the formula and the array is in a sheet named i7.

The formula piece i7.$L$1:$L$15 is converted to 'i7'!$L$1:$L$15 as it should.

The formula piece "i7.K" is not converted.

Perhaps I there is a better way to say "i7.K"&MATCH(..)?

Formula in Calc:

=INDIRECT("i7.K"&MATCH(B22,i7.$L$1:$L$15,1))+(B22-INDIRECT("i7.L"&MATCH(B22,i7.$L$1:$L$15,1)))*(INDIRECT("i7.K"&MATCH(B22,i7.$L$1:$L$15,1)+1)-INDIRECT("i7.K"&MATCH(B22,i7.$L$1:$L$15,1)))/(INDIRECT("i7.L"&MATCH(B22,i7.$L$1:$L$15,1)+1)-INDIRECT("i7.L"&MATCH(B22,i7.$L$1:$L$15,1)))

Formula when saved as xls:

=INDIRECT("i7.K"&MATCH(B22,'i7'!$L$1:$L$15,1))+(B22-INDIRECT("i7.L"&MATCH(B22,'i7'!$L$1:$L$15,1)))*(INDIRECT("i7.K"&MATCH(B22,'i7'!$L$1:$L$15,1)+1)-INDIRECT("i7.K"&MATCH(B22,'i7'!$L$1:$L$15,1)))/(INDIRECT("i7.L"&MATCH(B22,'i7'!$L$1:$L$15,1)+1)-INDIRECT("i7.L"&MATCH(B22,'i7'!$L$1:$L$15,1)))

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-11 17:37:33.346698

1 Answer

Sort by » oldest newest most voted
0

answered 2017-07-30 17:22:14 +0200

ptah gravatar image

This is a bug, or missing functionality. The formula piece "i7.K" should be converted to "i7!K"

I would like this reported as a bug please.

edit flag offensive delete link more

Comments

You can report bugs here

robleyd gravatar imagerobleyd ( 2017-07-31 02:25:23 +0200 )edit

thank you.

ptah gravatar imageptah ( 2017-08-01 15:46:34 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2017-07-30 16:13:17 +0200

Seen: 54 times

Last updated: Oct 11 '20