[Solved] Help converting from Supercalc

[davesuperc]

Been using Supercalc for many years. Have decided to convert to Open Office. Have problem with multi sheet spreadsheet - have loaded S1 and pasted S2-S4 into Open Office spreadsheet. All constants and variables are in named cells in S1 and appear in Navigator. Cannot figure out how to get references to them in S2-S4 to work. Format appears to be =Sheet1.'name' but does not bring up the S1 cell contents. Solution must be very simple but cannot find any help in the Help.

[acknak]

... All constants and variables are in named cells in S1 and appear in Navigator. Cannot figure out how to get references to them in S2-S4 to work. ...

If you have named ranges for them, all you need in the formula is name. No quotes, sheet name/number, just the defined name. In OOo, all named expressions are global; that is, the name is tied to a specific sheet.

[davesuperc]

Thanks for that Acknak. But for me it does not work. In sheet 2, so far, I have set the cell where I need the constant or variable from sheet 1 to =name that is the name as it appears in Navigator. Also tried =sheet1. cell address - no joy with either. I wonder if I missed something when I inserted sheets 2-4 and pasted in their contents.

[acknak]

"It doesn't work" doesn't really help much. What does happen, exactly?

You could try making a new spreadsheet, naming a cell & inserting a reference, just to see how (or if) it works.

To name a cell, just click on the cell, then type the name in the "name box" at the left of the formula toolbar above the cell grid.

To refer to that name, type = to get into formula mode, then start typing the name. Calc should automatically complete the name for you.

[davesuperc]

To recap - because the cell names are present in Navigator I assume that they have been loaded correctly - so in sheet 1 when I double click on a cell name in Navigator, the cell is highlighted which seems OK. But in sheets 2-4 when I highlight a cell with the supercalc reference and edit it to =name all I get is that string, same if I edit to =sheet1.celladdress. As you suggested, I tried it out on a blank 2 sheet s/sheet and, of course it worked OK. I have also tried typing =name into a blank cell on sheet 1 and all that I get is that string. I hope that I am not going to have to delete all the cell names and redefine them and the references on the other sheets - would take forever.

[acknak]

Ok, thanks.

The only thing I can suggest is to check the cell format: if that is set to text, then anything you enter will be taken as literal, verbatim text, without any further interpretation.

If that doesn't help, maybe you could attach the document file here and we could take a look directly?

[Villeroy]

Tools>Options>Calc>View: "Show formulas" = OFF. Otherwise you see formulas instead of results.
The cell's number format code should be "General" or anything else but not "@" (text). Number format code "@" supresses all evaluation.

[davesuperc]

Thanks Acknak and Villeroy - a step forward perhaps
1) in sheet 2 changed the cell >format>cell to number general
2) in sheet 2 the cell >tools>options OOo calc view display formulae is already unticked
3) edited the sheet2 cell contents to =name but it brought up the contents of the addressed cell but on sheet2 instead of sheet1
4) edited the sheet 2 cell contents to =sheet1.name but this brought up #NAME? not the same "name" of course, perhaps an error report?
regret cannot send a copy of the sheet because it contains confidential info

[acknak]

... 3) edited the sheet2 cell contents to =name but it brought up the contents of the addressed cell but on sheet2 instead of sheet1

It could be that this is a glitch in the import feature.

Go to Insert > Names > Define
Click on the name you're interested in and check the definition ("assigned to") at the bottom. My guess is that you'll see the reference to Sheet2 there--or, possibly, the definition has no reference to a specific sheet, so the name refers to the current sheet. You can add the "Sheet1." part to the definition, as needed.

I can't tell you the steps to change the definition there--that dialog window still confuses me after ten years of using it. You'll have to play with it until it works.

4) edited the sheet 2 cell contents to =sheet1.name but this brought up #NAME? ...

Once again, this is not how the names work--that's not a valid cell reference. You just use the name by itself.

[davesuperc]

Thanks Acknak
I have played around with >insert>names>define. From Supercalc each sheet had to be saved in a separate file in Lotus format. I think that the problem is that when I opened sheet 1 in OOo from Lotus format, sheet 1 was the only sheet so the cell names were not given a sheet number. When I add $sheet1. to their $cell address in >insert>names> define the =ref in another sheet brings up the correct constant or variable from sheet1. Unless you know of a way to do the job in Navigator, looks like I shall have to spend the holiday redefining all my cell names.

[acknak]

I know you can get a "dump" of the names, and I believe you can also define a set of names in batch, but I can't remember now just how that works, or whether it would help with your situation.

Maybe someone else can make a suggestion?

[Villeroy]

Insert>Names>Insert... [All] dumps all names to the active cell.
Insert>Names>Create... can use adjacent text as names.
Let A1:B3 be:
One 1
Two 2
Three 3
Select A1:B3
Insert>Names>Create... [X]From left column
reads the names from the left column and assigns them to the cells on the right.

[davesuperc]

Thanks. Have got my names defined ok as $sheet#.$celladdress. Now having trouble activating the references from other sheets. I highlight a range of cells and change the format to 'number general' but only the first cell changes. Cannot find a way round this. I type a reference as =cellname but it is not activated. I try again as ='cellname' and up comes #name? - so I erase the quotes and lo the correct named cell contents are dislplayed. This mystifies me. Any Ideas?

[acknak]

I wonder: is there any possibility that you could attach the file you're trying to import into OOo? I can't help but feel that there is something simple we're missing--importing a file shouldn't be this hard/broken. But I can't guess what it might be; maybe poking at the file itself will suggest something simple.

If you can attach the SCalc file, you'll probably have to first put it in a zip archive. Or, maybe you can upload it somewhere and give us a link.

[davesuperc]

Thanks Acknak. The problem is not importing - that seems to have worked - given the age of supercalc and the need to go via lotus wk1. Its a very long time since I used Lotus at work. The data is all there in OOo - it is just tweeking the Supercalc standards to OOo standards. I have solved the second mystery - typing = does not worlk- one must use the formula bar = and then the tick to activate. But I am still puzzled by >format>cell>number - if I format 2 or 3 cells to number/general it seems to work but if I then look at just one of the cells it still shows text/@. And I cannot find a way finding out a cell''s format just by highlighting it. So I can progress with tweeking. Hopefully, there will be no problems when I get to formulae. I shall let you know.

[davesuperc]

I have solved the format problem - searched and searched and eventually found the answer in HELP - don't use >format, use the format bar button. Have got the IF formola working - similar to supercalc. See what else I stumble over.

[davesuperc]

Thanks Acknak and Villeroy. I have completed my learning curve and have got this s/sheet wotking OK in OOo. I can now convert the others. Best wishes for 2013.