Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Auto-calculate cell values returned by Excel 4, ie =GET.CELL() formulas in Excel
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:
...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):
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.
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
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):
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.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Hi WeThotUWasAToad,
To get the Excel4 macro functions to update you have two options (at least):
1) Do a manual 'full calculate' by pressing Ctrl-Alt-F9
2) Use VBA to do a 'full calculate', for example, whenever something changes on the worksheet by adding code to the worksheet module:
Unfortunately, the latter won't be triggered by a change to a column width (for example), but if you change any cell contents (for example), then it will be.
You could also add that to any other worksheet event that you like (activate, selectionchange, whatever you like).
Alan.
To get the Excel4 macro functions to update you have two options (at least):
1) Do a manual 'full calculate' by pressing Ctrl-Alt-F9
2) Use VBA to do a 'full calculate', for example, whenever something changes on the worksheet by adding code to the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.CalculateFull
End Sub
Unfortunately, the latter won't be triggered by a change to a column width (for example), but if you change any cell contents (for example), then it will be.
You could also add that to any other worksheet event that you like (activate, selectionchange, whatever you like).
Alan.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Thanks for the responses.
2017-11-23_EE_Excel-4-formulas-.xlsm
Thanks, Steve
Comment by: Ryan ChongSorry about that Ryan. I was thinking the whole time while writing my OP that I would be attaching the file and then forgot to do it. Here it is:
can you provide that excel sample file here as well?
2017-11-23_EE_Excel-4-formulas-.xlsm
Thanks, Steve
Hi Steve,
Does that mean the above does not work for you?
If not, please post back with how it doesn't work, and what you need instead.
Thanks,
Alan.
Does that mean the above does not work for you?
If not, please post back with how it doesn't work, and what you need instead.
Thanks,
Alan.
Hey Alan, sorry about that. I did look at your comment when I was on earlier but then only had enough time to reply to one post. :P
— although I'm not sure why they specify "dependent" formulas in the last one since it seems like every formula is dependent on something.
Regarding your VBA solution, I don't think that would be as useful because I am specifically interested in seeing the current width setting of the column — including when it changes.
Therefore, you've given me a sort of semi-automatic solution which is not quite the same as fully-automatic but better than single-shot. :)
Thanks, Steve
Comment by: AlanI don't remember using that shortcut {Ctrl+Alt+F9} in the past so thanks for including it. I actually googled it looking for more info and came across the following on Microsoft's Excel shortcuts page (at https://support.office.com/en-us/article/Excel-keyboard-shortcuts-and-function-keys-for-Windows-1798d9d5-842a-42b8-9c99-9b7213f0040f):
To get the Excel4 macro functions to update you have two options (at least):
— although I'm not sure why they specify "dependent" formulas in the last one since it seems like every formula is dependent on something.
Regarding your VBA solution, I don't think that would be as useful because I am specifically interested in seeing the current width setting of the column — including when it changes.
Therefore, you've given me a sort of semi-automatic solution which is not quite the same as fully-automatic but better than single-shot. :)
Thanks, Steve
Yes, but F9 if insufficient whereas Ctrl-Alt-F9 is sufficient.
There is no need in your situation to go to Ctrl-Alt-Shift F9 - no harm, but it might take longer in some situations - mostly it is just another key to find :-).
Alan.
There is no need in your situation to go to Ctrl-Alt-Shift F9 - no harm, but it might take longer in some situations - mostly it is just another key to find :-).
Alan.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.