troubleshooting Question

Auto-calculate cell values returned by Excel 4, ie =GET.CELL() formulas in Excel

Avatar of WeThotUWasAToad
WeThotUWasAToad asked on
Microsoft OfficeMicrosoft Excel
6 Comments1 Solution113 ViewsLast Modified:
Hello,

Is there a way to auto-calculate cell values returned by Excel 4, ie =GET.CELL() formulas in Excel?

I've been experimenting with Excel 4 formulas for a way to display Column widths and/or Row heights in Excel and took the following definitions:
   6   Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.

    17  Row height of cell, in points.  

    42  The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell.
            May be a negative number if the window is scrolled beyond the cell.

    44  The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell.  
            May be a negative number if the window is scrolled beyond the cell.

    Tip Use GET.CELL(17) to determine the height of a cell and GET.CELL(44) - GET.CELL(42) to determine the width.

...from the list of formulas posted here:
    https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Row 8 in the following screenshot, shows manual column-width settings for columns B:F and Row 3 shows column widths for the same columns as returned by the formula named LR_Width (see below):

2017-11-23e.png
The next screenshot includes the Name Manager in order to display definitions of the formulas in use which, for cells F1:F8, are manifest in column G by use of the formula named =zFormula.

2017-11-23f.png
The formulas work great until the width of one or more of the columns is changed — in which case, the Excel 4 formulas do not automatically update. In addition, found that clicking

    Formulas menu > Calculate Now

is not recognized by the Excel 4 formulas as it would be by a typical Excel formula, e.g. =RANDBETWEEN().

The only way I could find to update the values was to click in the Formula Bar then press {Return} — and this process had to be repeated for each cell containing an Excel 4 formula. I was surprised by this result because I have been using =GET.CELL(6,F1) for many years and it has always auto-updated immediately when making a change to the referenced cell.

Am I missing something or is there a trick that will cause all the Excel 4 formulas to auto-update?

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros