workaround absolute cell references

[Greg]

Hi all,

I am trying to find a way to avoid the absolute cell referencing problems in Word.

Imagine a simple table of 3 columns. The first column is used to write a description, while the second column is used to add a condition that will affect the value of the third column (which contains a dollar value).

I want to place an asterisk (or figure) into the second column so that another cell (elsewhere in the table) calculates 10% of the amount in the third column.

If you haven't guessed already I am trying to calculate Goods and Services Tax (GST) in a table where some amounts are GST free and then total all the amounts plus the GST.

Of course all of this is fairly easy to achieve with a formula if the table has a set number of rows and columns. However, if I add another row to the table I lose my formula but even if I didn't, the formula references would be incorrect for the new row.

I know that Excel overcomes all these problems but for some unknown reason is an aversion to it in this office.

My solution was to split the table (the split is not visible in the printed document) and use bookmarks to separately calculate those amounts which are subject to GST and those that aren't.

By then adding the amounts of the bookmarks together I get the grand total including the GST. Although this works it is not ideal.

I can't believe that there isn't some kind of workaround for this. Can anyone help?

Regards,

Greg.

[fumei]

The workaround is to use the proper tool for the task...which is a spreadsheet not a word processor. Whether there is an aversion or not, the reality is Word is a word processor. It has never been designed to do this stuff well, and....it never has. If powers that be in your office have such an aversion...tell them to grow up.

Actually your solution of using bookmarks is rather inventive. I'm sorry, but really, Word is very very very crappy with doing this. That is because...it is a WORD processor.

[mdmackillop]

If you're still stuck with this, a completely different approach.
Use the dimensions of your table to create a userform, picking up text values as required, doing your calculation there and writing the results back to your table.
Good luck!

[Greg]

Hi Malcolm,

Do you have an example to show me?

My problem is complicated by the need to add rows. Can your method do that?

I get around the problem by using a split table with virtually invisible joins between one table and the next but if extra rows can be added using your method, then that may be better.

Regards,

Greg.

[mdmackillop]

Purely theoretical. Post a copy of a typical table and I'll maybe get time at the weekend.

[Greg]

Hi MD,

Thank you for your offer of help.

The document I am working on is attached.

Greg.

[fumei]

Greg, it seems to me that your bookmarks solution IS the workaround. It seems to work decently. What exactly is the problem?

[Greg]

Thankyou.

If I use it there isn't a problem but I'm worried that somone else may inadvertantly delete a bookmark. That's why I had hoped there might be something more foolproof.

Greg.

[fumei]

Ah...yes, that is a danger. I am not sure what you can do about it. The only thing off the top of my head, is back to Malcolm's suggestion. You could do everything on a userform, rather than in the document.

If the document is locked for forms, then you could put the results of the userform into the document safely.

[Greg]

Hi Gerry,

I am now quite familiar with userforms but I am not sure what Malcolm had in mind. If you know perhaps you could give me some clues.

greg.

[Greg]

Hi Malcolm,

Did you get a chance to consider how I might apply a userform to my document?

Greg.