Calculating with hidden cell in Word 2010

Topic

New Reply

In 2007, and earlier, you could do your calculations in a Word table in hidden cells and then present the answer in another cell. This made the presentation tidy. Now it appears that 2010 ignores any data in a hidden cell so everything has fallen apart! Is there any way to turn off this unwanted “feature”?

Reply | Quote

Replies

Hi Judith,

You can also do the calculation in-situ, without the need for a hidden cell (which could never be hidden reliably anyway). Simply create a field via Ctrl_F9, and input the formula for your calculation (eg {=MOD(513*37,28)}). When you’re done, press F9 to display the result. A bonus is that this can be done anywhere in the document. To edit the formula, simply select the displayed result & press Shift-F9 to expose the field code (alternatively, Alt-F9 without even needing to select anything exposes all field codes).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Judith,

You can also do the calculation in-situ, without the need for a hidden cell (which could never be hidden reliably anyway). Simply create a field via Ctrl_F9, and input the formula for your calculation (eg {=MOD(513*37,28)}). When you’re done, press F9 to display the result. A bonus is that this can be done anywhere in the document. To edit the formula, simply select the displayed result & press Shift-F9 to expose the field code (alternatively, Alt-F9 without even needing to select anything exposes all field codes).

Problem is the whole thing is being automated for naive users – who have had this working beautifully for over 9 years! They know nothing about fields. They fill in a userform and some quite complex calculations are done on their behalf. It needs to be on the page so they can alter them if need be – for instance partially paid land taxes where the dates may change or the council proportion may alter. Layout too is vital.

Reply | Quote

Hi Judith,

I wouldn’t have thought creating a field in the way I suggested was beyond even a novice user. Still, if your userform were to have a textbox into which the user could enter a formula, and a command button to process it, you could use code like the following to put the result into the document:

Code:

Private Sub CommandButton1_Click()
Dim myRange As Range
If Left(TextBox1.Text, 1) = "=" Then
  Set myRange = ActiveDocument.Range(0, 0)
  With myRange
    .Text = TextBox1.Text
    .Text = .Calculate
  End With
End If
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I wouldn’t have thought creating a field in the way I suggested was beyond even a novice user.

Actually it is. But what you are saying is there is no way to turn off this extremely irritating change in behaviour so we’ll have to go through all the forms/documents that have used this for years and recode.

Reply | Quote

Hi Judith,

No, I’m not saying that, though that may well be required. What I’m saying is that trying to hide a cell’s contents is unreliable, since the display and printing of hidden text is controlled by the user at the application level and can’t be controlled at the document level.

If any recoding is required, it may be nothing more than turning on the hidden text display for the duration of the calculation, then turning it off again. Without seeing a sample document, I can’t give a definitive answer, though.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

At application level everyone has update codes on print and do not print hidden text – and I am talking about users who don’t even know such options exist let alone likely to change them. I’ve started on the first document that I noticed this on – but I know there there more out there (an unknown number as some completed documents are stored as precedents and there are thousands – although most do not include calculations) – and existing one off documents that were right when saved but if they are re-printed will give garbage (update codes on).

Reply | Quote

Hi Judith,

I’m not sure what the issue is with your document, but the attached one (which I created in Word 2010) has two tables, both with the same formula in A1. These calculate correctly via a field in A3 and via the macro in the document. Note that the text in the hidden cell is not itself hidden. I’ve tested the document in both Word 2003 and Word 2010 and the results are the same (including with a doc version). If the contents of A1 are formatted as hidden text, no result is obtained from either the field code or the macro in either version of Word.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

There are lots of different scenarios. The simplest I have already coded around – but I’ve attached the orginal. There is a list of disbursements – narration, amount and the total appears in the bottom right column (C). Only if there is only one disbursement what was the subtotal in column B is hidden. This meant the secretary could add another line in the table and just update fields (and unhide the text) – the new amount would be added in. To get around this I have now hard coded the total for a single disbursement which means if they want to add a line they need to do it via a macro which will replace the hardcoded total with a formula.

Reply | Quote

Hi Judith,

In your previous posts, you referred to the cell being hidden. In your attached document, the cell isn’t hidden – the text is. The formula you have in cell C1 of the ‘Tolls/Phones/Faxes’ table only works in Word 2003 – and 2010 – if that hidden text is visible. Consequently, I can’t see what the issue of changed behaviour in Word 2010 is supposed to be as the behaviour is the same.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Judith,

In your previous posts, you referred to the cell being hidden. In your attached document, the cell isn’t hidden – the text is. The formula you have in cell C1 of the ‘Tolls/Phones/Faxes’ table only works in Word 2003 – and 2010 – if that hidden text is visible. Consequently, I can’t see what the issue of changed behaviour in Word 2010 is supposed to be as the behaviour is the same.

Just checked it – and you are right. But it does work in 2007. So hopefully it is not as widespread as I feared. Just stuff created in 2007.

Reply | Quote