[Solved] Invalid Sheet Ref. when selecting rows to repeat

Discuss the spreadsheet application
Post Reply
jle101
Posts: 4
Joined: Wed May 20, 2009 9:37 pm

[Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by jle101 »

I am new to forums. I am running Ubuntu 9.04 and Open Office 3.1.0. I am trying to select the first three rows of a single sheet spreadsheet to repeat on all pages when printed. I have selected Format-Print Ranges-Edit, clicked on the icon to the right of the input field and used the mouse to select rows 1 through 3. When I click OK I get the following error message: invalid sheet reference. I have used this feature in prior versions of Open Office and it worked fine. I would appreciate help on this issue. I have done a search on this site and a google search, with no success.
Last edited by jle101 on Fri Oct 23, 2009 8:43 pm, edited 1 time in total.
OOo 3.1.X on Ubuntu 8.x
Nikos
Posts: 175
Joined: Mon Dec 17, 2007 11:50 am

Re: Invalid Sheet Reference when selecting rows to repeat

Post by Nikos »

Does this problem occur with all your documents?
Else, maybe you could upload an example. I cannot replicate this problem on 3.1 running on W2K (OpenSuse is still on 3.0.1).
LibreOffice 25.2 on Kubuntu 25.4 (flatpak) + LibreOffice 25.2 on Ubuntu 25.4 (flatpak)
jle101
Posts: 4
Joined: Wed May 20, 2009 9:37 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by jle101 »

Thanks for responding. I should note that Open Office 3.1.0 is a new install. I did not have this problem in a previous version but have it with all of the documents that I work with in 3.1.0. I will attempt to upload a screen shot.
InvalidSheet.png
OOo 3.1.X on Ubuntu 8.x
User avatar
Hagar Delest
Moderator
Posts: 33709
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Invalid Sheet Reference when selecting rows to repeat

Post by Hagar Delest »

Works fine for me with 3.1 on my xubuntu Hardy box. You should try the official (Sun) version: [Ubuntu] Installing OOo on Debian and Co.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
LibreOffice 25.2 on Linux Mint Debian Edition (LMDE 7 Gigi) and 25.2 portable on Windows 11.
jle101
Posts: 4
Joined: Wed May 20, 2009 9:37 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by jle101 »

Re: comments from Hagar de L'Est, Thanks for your input. I read the tutorial (http://user.services.openoffice.org/en/ ... ?f=16&t=68) along with all of the posts. It appears there are more problems with installation from the vendor than I currently have.

After more research it appears the invalid sheet reference has been identified as bug # 374708 at Launchpad <https://bugs.launchpad.net/openoffice-pkgs/+bug/374708>. It is also identified as bug # 527535 at Debian.org <http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=527535>. There is probably nothing more to do until the community comes up with a patch to correct this problem.
OOo 3.1.X on Ubuntu 8.x
User avatar
Hagar Delest
Moderator
Posts: 33709
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Invalid Sheet Reference when selecting rows to repeat

Post by Hagar Delest »

Have you installed the Sun version? You won't have this bug then.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
LibreOffice 25.2 on Linux Mint Debian Edition (LMDE 7 Gigi) and 25.2 portable on Windows 11.
saskatchewan
Posts: 25
Joined: Fri Apr 04, 2008 4:11 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by saskatchewan »

Which is the "Sun" version?

Do you mean uninstall the version from the repositories and then do a download from the website?
thomasjk
Volunteer
Posts: 4456
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Invalid Sheet Reference when selecting rows to repeat

Post by thomasjk »

Hagar means to download from here http://download.openoffice.org/other.html#en-US rather than any other site.
Tom K.
Windows 11 24H2
LibreOffice
arnel
Posts: 1
Joined: Fri Oct 23, 2009 7:31 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by arnel »

Klaus Stengel reported a workaround without having to download the sun version:
https://bugzilla.novell.com/show_bug.cgi?id=502641#c12

The problem is that the syntax check in areasdlg.cxx fails if there is no
formula grammar specified in the user configuration. In that case
lcl_CheckRepeatOne() just returns "false" instead of trying with the default
grammar. As a workaround try the following:

1. Select Tools/Options... from the menu
2. In the left tree navigate to OpenOffice.org Calc/Formula
3. Click on the Arrow right to "Calc A1" to see the list of possible grammars
and select "Calc A1" explicitely from the dropdown list (even if it was already
selected)
4. Press "OK" to save

If that doesn't work at once you may try to select some other grammar, save,
and then revert back to "Calc A1" and save again. Please report if it worked
for you or not.
OpenOffice.org 3.1.0
Ubuntu 8.10
jle101
Posts: 4
Joined: Wed May 20, 2009 9:37 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by jle101 »

Thank you Klaus Stengel. I have not installed the Sun version as the installation process requires a lot of input through the terminal, and as a novice, I am not comfortable with that. I tried your workaround on two sample spreadsheets with success both times. Thanks again. :)
OOo 3.1.X on Ubuntu 8.x
algotruneman
Posts: 1
Joined: Mon Nov 16, 2009 10:35 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by algotruneman »

The fix from Klaus Stengel worked for me, too, but it also turned off the freeze (Window menu - freeze) I had put on the top three lines of the sheet so they would remain visible during data entry once the sheet got large. Putting the freeze back removed the print range selection of those same three lines that the fix dealt with.

Using the (Window - split) to accomplish the same scroll benefit did not turn off the print range.
The "incompatibility" of freezing the top three rows with selecting them to be printed may be where the source of the problem is, and though the work-around is successful, it isn't, apparently, a fix.

Left a comment on the Ubuntu Launchpad bug 374708.

Update: Further fiddling seems to indicate that since I used the split option for the window, the freeze option no longer disables the printing selection of the three lines.
OpenOffice 3.1.0 Kubuntu 8.04.2
stiffneck
Posts: 2
Joined: Sun Jan 10, 2010 6:10 am

Re: [Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by stiffneck »

Thank you this worked for me!
"Leadership means breaking the status quo." -Just look for the one who said this in Google - OpenOffice 3.1 on Ubuntu 9.10
lp3825
Posts: 1
Joined: Sat Jan 16, 2010 9:23 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by lp3825 »

Thanks that worked for me.
Louis Passano III - Maryland
OpenOffice 3.1.1 on Ubuntu 9.10
User avatar
ubundom
Posts: 11
Joined: Wed Nov 28, 2007 1:47 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by ubundom »

@arnel: Thanks, that worked for me too using OOo 3.1.1 OOO310m (Build:9420) on Linux 2.6.31-19-generic on a Dell Latitude D810.
Ubuntu 9.10 Karmic on a Dell Latitude D810 laptop
Migrated from Openoffice.org 2.4.1 to 3.1.0
Baxter Basics
Posts: 1
Joined: Wed Mar 10, 2010 8:53 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repeat

Post by Baxter Basics »

I just had the exact same problem.

All I had to do was:
select the Format - Print Ranges Edit
Rows to Repeat - user-defined
I entered 1:4 instead of what I really wanted which was A1: R4
OO changed it to $1:$4, and all is well!
Open Office 3.0.0 on Windows XP Home / Open Office 3.0.0 on Ubuntu 8.4
charismaticjoshi
Posts: 1
Joined: Tue Jun 22, 2010 6:02 am

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by charismaticjoshi »

Yes the information was very helpful... Thanks a lot!!!
OpenOffice.org 3.1.1
OOO310m19(Build 9420)

Ubuntu 9.10 (kernel 2.6.31-19-generic) Gnome 2.28.1
amanda
Posts: 3
Joined: Tue Mar 25, 2008 2:07 am

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by amanda »

Works for me, too. Thank you! I was tearing my hair out until it dawned on me to try searching for
repeat rows "invalid sheet reference"
. Thank you!
Be4Truth
Posts: 2
Joined: Mon Dec 03, 2007 12:03 pm

Re: Invalid Sheet Reference when selecting rows to repeat

Post by Be4Truth »

arnel wrote:Klaus Stengel reported a workaround without having to download the sun version:
https://bugzilla.novell.com/show_bug.cgi?id=502641#c12

The problem is that the syntax check in areasdlg.cxx fails if there is no
formula grammar specified in the user configuration. In that case
lcl_CheckRepeatOne() just returns "false" instead of trying with the default
grammar. As a workaround try the following:

1. Select Tools/Options... from the menu
2. In the left tree navigate to OpenOffice.org Calc/Formula
3. Click on the Arrow right to "Calc A1" to see the list of possible grammars
and select "Calc A1" explicitely from the dropdown list (even if it was already
selected)
4. Press "OK" to save

If that doesn't work at once you may try to select some other grammar, save,
and then revert back to "Calc A1" and save again. Please report if it worked
for you or not.
This worked for me on Lucid 32 bit with OOo 3.2.0
mosaic2s
Posts: 3
Joined: Sun Oct 26, 2008 6:50 am

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by mosaic2s »

Ditto.
Ubuntu 10.04 with open office 3.2.0.
Thanks for the simple tip.
OOo 3.0.X on Ms Windows XP + ubuntu 8.04
gnulab
Posts: 5
Joined: Tue Nov 18, 2008 12:15 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by gnulab »

This trip provided solved my case too.

I'm using Ubuntu 10.10 (Maverick)
OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-7ubuntu1
OOo 2.4.X on Ms Windows XP
Gavin
Posts: 2
Joined: Wed Apr 27, 2011 11:59 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by Gavin »

Good deal. Re-starting the CalcA1 grammer worked for me and I didn't lose "window freeze" either.

Thanks.
openoffice 3.2.0 on Ubuntu 10.04LTS
gian
Posts: 15
Joined: Thu Apr 07, 2011 9:45 am

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by gian »

well this was very useful indeed.
OpenOffice 3.2 - Ubuntu 10.4
biochemistrygmcs
Posts: 1
Joined: Mon Feb 27, 2012 12:56 pm

Re: [Solved] Invalid Sheet Ref. when selecting rows to repea

Post by biochemistrygmcs »

Options->Openofcie.org calc->Formula->Formula Options0>formula syntax-> deselect Calc A1-> again select Calc A1 and save. It worked for us
Ubuntu , open office 3.1
Post Reply