I use Labels in Formulas extensively. (Excel 2003 11.5612.5606)
That is, I tick off Options/Calculations/"Accept Labels in Formulas"
and then use whatever text string I've placed at the top of a column
of data to refer to it.
It has many benefits over Named Ranges, dynamic or otherwise for my
purposes, but one ongoing annoyance.
When I add data to the bottom of the column of data, the in-cell
formula using the label for reference does not change when it should.
Workaround:
Select cell, F2 or double click to open it, Enter.
I have a number of such formulae so updating can be a pain.
Is this expected behaviour?
Does anyone know how to update them en masse (without resorting to VBA
or other automation techniques)?
Henn Sarv - 06 Mar 2008 06:25 GMT
Try following
Ctrl-H (edit-replace)
Find "="
Replace to "="
and press Replace All
In my test - it works fine
Henn
>I use Labels in Formulas extensively. (Excel 2003 11.5612.5606)
>
[quoted text clipped - 16 lines]
> Does anyone know how to update them en masse (without resorting to VBA
> or other automation techniques)?
inews@questuscorp.com - 10 Mar 2008 01:02 GMT
Thanks Henn,
Will script it and put it in a "Refresh" button on the worksheet.
Does anyone else have any further tips?
> Try following
>
[quoted text clipped - 32 lines]
>
> - Show quoted text -