Tabular Projections - Presentation & Subscript Errors
I am experiencing a maddening problem with the Tabular Projections page in PRC Gold. I have seen this mentioned in another post but wanted to elaborate a little more in hopes of helping to troubleshoot the cause and am posting here (in a regrettably long post) instead of via email in case other users are experiencing the same phenomenon and may have some input. As a new PRC user, I am still learning how the various data inputs are digested and translated into the various financial measures that I am scrutinizing. I have found the easiest way to do this is to enter/modify some data and then flip over to the Tabular Projections page to see the resulting impact in its rawest form. I am doing this iteratively, several dozen times per session of PRC use, as there is so much to learn.
The initial view of Tabular Projections presents itself in some sort of funk (see attachment):
- The first row comprising the Main Menu (Home, Financial Assets, Income, …) is not visible.
- The next 8 or 9 rows (depending on previous zoom level?) are frozen, but some of the frozen rows are data rows (not just headers).
- Clicking in a cell and using the Up arrow (to navigate up to the missing Main Menu) results in a “Subscript out of range” error.
With a hat tip to the other poster, I learned that by manually changing the Zoom level (Excel’s, not PRC’s on the Home page) the proper rendering of the Tabular Projections page is restored. After navigating away, however, the process must be repeated upon each subsequent return. Every. Single. Time. (Hence that “maddening” up there. 😬)
- PRC Gold PRC2021.3.4
- Bats’ eyes are sharper so the first thing I did when I opened up PRC and noted that Zoom link on the Home page was to set the default Zoom level to 150%. (Great feature!) After my Tabular Projections tribulations, I changed that default Zoom level back to 100%. I wonder if my customizing the default Zoom is the root cause? And that even by my resetting it to 100% that PRC still sees it as a “custom" setting since I explicitly typed in 100 to “reset” it? It does remember my previous manual Zoom level upon return, but it no longer likes that one; I have to select another zoom level every visit.
- Mac OS Mojave 10.14.6 (18G9216). Perhaps you are targeting newer Catalina, Big Sur?
- Intel Mac, not M1.
- Excel for Mac 2019, Version 16.5 (21061301). I believe I recall other mentions of 2019 eccentricity?
- Hiding/Showing Excel’s menus (Home>Hide Excel Menus) has no effect.
- Running Excel in Mac’s Windowed vs. Full Screen mode has no effect.
- I recently began tweaking the eight customizable Tabular Projections views, but this problem presented beforehand.
- To rule out #2 & #8 above, I downloaded a pristine version from the website, clicked “I acknowledge that I have read this page”, Option to Start Simple>No, clicked Tabular Projections. Perfect! Clicked Home. Clicked Tabular Projections. Perfect!! Clicked Home. Clicked Tabular Projections. Womp womp. No other intervening clicks or entries made.
- Repeated #9 above. This time Tabular Projections was messed up on the very first visit. I closed down Excel completely between opening the two pristine copies. No consistently repeatable pattern is apparent.
Attached is a screenshot of the wayward Tabular Projections>View Management page.
I hope someone has something else to contribute to help figure this out.
Here is a Tabular Projections>Income screenshot better illustrating the presentation problem (no personal data). The Tabular Projections>View Management page screenshot posted earlier was not very helpful in retrospect.
I appreciate that this is a maddening problem and that you've taken the time to describe it in great detail. First, there's been no effort on my part to target any particular platform or software version. I try hard to make PRC work on all platforms/versions. Of course, that's a moving target.
So, I played with this just now on my newest Mac platform which is very similar to yours and I was able to replicate the problem but, as you said, the behavior is not consistent. The thing I can report is that for some reason Excel is going into the split screen mode occasionally, and that's what you're seeing (as clearly shown in your screenshots). When this occurs, Excel loses the information I designed in to make the page (i.e., the page that's active when the problem occurs) scroll up/down and left/right correctly. There are two ways to escape this, and one is better than the other but harder to perform. The easiest way to escape this is to go to Excel's View menu and click the Split button a couple of times to get out of the split screen mode, and then put the cursor down in the PRC portion of the screen and scroll down to bring the navigation bar (i.e., the top of the page) back into view. Unfortunately, this still results in loss of the intended scrolling parameters and the entire screen scrolls (such that the nav links disappear when you scroll down and the year/age fields disappear when you scroll right). The better way is to put the cursor in the upper lefthand corner of the portion of the page you want to scroll up/down and left/right and then (on Excel's View menu) click Unfreeze Panes to eliminate all existing scroll controls and then click Freeze Panes (actually the same button and with the cursor correctly positioned). This will return the page's scroll controls to where they're intended to be. Generally speaking, you want to place the cursor on the first row of data and on the first column to the right of the year/age column before clicking Freeze Panes so that when you scroll the upper part of the page and the year/age fields are always visible. When I've done this the problem disappears for a good while but does reoccur from time to time. I've never ever seen this occur on a Windows platform so I think it's some quirk of Excel for Macs. In PRC2021.3.3 I attempted a fix but it turns out that I actually made the problem worse, so I backed out the change. I'm still trying to determine some code I can put into the navigation macros to work around this issue but as of now don't have any solution. In any case, hopefully the guidance I've provided here will alleviate the issue sufficiently that you can use the tool effectively.
Please let me know to what extent this helps on your system.
Thank you very much for the prompt reply and the detailed guidance. Both of your suggested workarounds were effective in restoring Tabular Projections to the desired state. As I hop around amongst the various Tabular Projections sub-pages (Income, Expenses, Taxes, Summary, …) all is well.
As you mentioned, the fix, however, is temporary and does return (semi-predictably but nowhere near repeatably) after this sequence:
- Navigate away from Tabular Projections
- Click back on Tabular Projections (all is well - current page is still OK)
- Click on another Tabular Projections sub-page (split screen problem comes back - sometimes)
So it looks like we have three alternatives for working around this problem at the moment:
- Change the Zoom level
- Toggle Split on then off
- Unfreeze Panes then Freeze Panes (with the cursor in the left-uppermost cell you wish to be scrollable)
Of the three workarounds, “Change the Zoom level” seems to be the shortest-lived. It works brilliantly with the least amount of effort but lasts only until navigating away from Tabular Projections (the failure upon return is near-predictable). At least the other two solutions you outlined survive a little longer. As a matter of fact, after about 30 minutes of experimentation, sometimes I couldn’t even make it break again. 🙄 Hard to pin this one down.
Thanks for the tips! I’ll be using them. I feel bad just carping about this and sincerely wish I had some Excel VBA skills to offer some ideas. Alas, I do not.
@ilovemybeagles Thanks for the follow-up, David. No problem with the carping because, prior to your posting, I had been under the impression this issue occurred only rarely. I now realize it can be frequent which greatly increases my motivation to figure out some sort of solution.
Stuart, another hint regarding this issue....I have one Intel iMac running Excel 2019 and another Intel Macbook running Excel 2016. The problem doesn't occur on the Macbook with Excel 2016.
@mcghee659 Thanks, Frank. Right, I think this problem may be unique to Excel 2019 for Macs.