[Solved] Preserving Number format and formulas in a template

[LuAnn4220]

I am trying to create a template that will contain a large number of tables. The tables will contain formulas in some cells, and a very specific number format in all cells.

I've attached an example. I created the first table and filled in the data so that I could get the number format and calculations in place. Then I copied the table and changed all the numbers to zeros. (The "Expression is faulty" message shows up because of the zero in the denominator. We can live with that because the zeros will disappear once the table is filled out, of course I'd prefer an "if/then" style formula, but that is beyond my skill level at this point in time!)

My questions:

Will number format "hold" if I remove the cell contents? (Do I need those zeros as "placeholders?")

What is the easiest way to assure that the people who USE the template don't lose the number format and/or formulas?

Is there a way to "lock" the number format of a cell and still allow the user to change the cell contents?

I am still very new at this (obviously) and I will need to make a large number of these tables, so i want to do it in the most efficient manner possible.

[acknak]

Will number format "hold" if I remove the cell contents?

Yes.

What is the easiest way to assure that the people who USE the template don't lose the number format and/or formulas?

Is there a way to "lock" the number format of a cell and still allow the user to change the cell contents?

Use a different application. Writer would be a poor choice for doing this, IMO. Entering data in Writer tables is very awkward, and if you happen to make a mistake and enter something like "9O" (use a letter "O" for a zero), then Writer will automatically change the cell's number format, leaving the user with a complex repair job.

You can protect cells, but I think in that case, you can't enter anything at all.

...the zeros will disappear once the table is filled out, of course I'd prefer an "if/then" style formula, but that is beyond my skill level at this point in time!)

Don't sweat it: it's beyond Writer's capability as well. I don't know of any way to write a formula that can hide an error like this. You can sort of kludge it with something like =(<J5>/(max<D5>|1))*100, but that only works if you'll never values less than one in cell D5, and it clutters the purpose of the formula.

If you want easy & safe data input, I would use a spreadsheet (easy to set up but some drawbacks), or better, a form (harder to set up, but much more robust).

I learned something from your document: the formula =<D5:D7> is equivalent to =SUM <D5:D7>. Ugh.

Formulas and math in Writer tables are so strange and fragile that they're almost not worth using, IMO. Better to use something that can do a better job with the numbers and leave Writer to handle the text formatting and layout only.

[LuAnn4220]

Use a different application. Writer would be a poor choice for doing this, IMO. Entering data in Writer tables is very awkward, and if you happen to make a mistake and enter something like "9O" (use a letter "O" for a zero), then Writer will automatically change the cell's number format, leaving the user with a complex repair job.

I was very much afraid this would be the answer. We've experienced EXACTLY the circumstance you noted (user accidentally inserts text in the cell and removes the number format)

Unfortunately, the "average" document that comes out of our offices combines a HUGE number of text and tables, and my bosses want a template that has everything already in place in a single document.

Thanks for the information!

[acknak]

Well, the boss is always right

The way this is done right is by a form that connects to a database, so the user has something nice and robust to enter the data, and it can go directly into the database.

From there, you create a report that extracts the proper values from the database, does the necessary calculations, and generates a Writer document as output. So far, none of these steps require any manual intervention at all.

At this point, you can take the report document and tweak it as needed to get your final product.

The down side is that this takes a significant effort, and a lot of technical skill, to set up. Most organizations just aren't able to handle it effectively. The compromise is to use something that they understand better, even if it is not as good a solution.

With a Writer document, you might want to consider using fields in the table. Placeholder fields allow editing in the table, but the cells must not be protected. The input fields will work in protected cells, but must be edited in a pop-up window.

Maybe there is a better choice; I'm not an expert on this.

Here's a sample:

[LuAnn4220]

The down side is that this takes a significant effort, and a lot of technical skill, to set up. Most organizations just aren't able to handle it effectively. The compromise is to use something that they understand better, even if it is not as good a solution.

EVENTUALLY, I expect all of this will be a part of the "shell" that is being built for us around OpenOffice by a team of experts who are light-years ahead of me.

Our office is the last group to have our documents converted to the new system, from WordPerfect to OpenOffice (plus). So we're somewhat in limbo, trying to learn some of the basics of OpenOffice while waiting for the experts to show us more.

Trouble is, all the documents need to LOOK just like they did in WordPerfect. (And for years everyone just copied and pasted new numbers into old tables.) I'm trying to make sure the tables do the calculating, instead of doing the data-entry twice (once in Excel to do the calculations, then copied and pasted into WordPerfect. UGLY way to work....)

BOTTOM LINE: I'm very grateful for all the help I get from this forum!

P.S. I've tried to work with fields a little bit, but I'm not skilled enough to make input fields for all the tables that appear in the particular template that is due soon. Thank you for the sample. I'm sure it will help me once I delve into the "fields issue."