Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Home > Forums > Excel
Excel Forum

Back to Excel Forum

Formula Management Upload Images   Link  
James Allen 
5 months ago
I Have many worksheets that have huge formulas in them. When I come back in after a few months or years I look at the formula and in my head hear Doc Brown Yell "1.21 Gigawatts !!, Tom what was I thinking , How could I have been So Careless?"--
I wrote the formula and it still took  me an hour or more to figure out what I did.. I recently found out that you can use alt-Enter to add linefeeds to the formulas in the cell to make them more readable... are there Other controls to allow formatting of the actual formula itself in the cell to make it more readable, specifically it would be great to be able to indent formula also... ?? any ideas?

Alex Hedley
5 months ago
fx: The formula bar also stretches to more than one line
James Allen
5 months ago
Thank you Alex,
My problem is even with the extra lines the formula is so complex that it very hard to parse in my head... the alt-ent linefeed allows the syntax to be separated and more easily viewed without affecting the  operation... but still I was hoping for additional formula management like indents... I did also just today found where folks are using +N() as a pseudo comment field in the formula...
for example here is a normal formulas for my sheets..
=IFERROR(IF(T(C3)<>"",VLOOKUP(C3,'Y:\Main\Telcomm\SPPC Ckts\a). Indexes\[CIRUIT MASTER LIST.xls]Sheet1'!$A$2:$B$50002,2,0),IF(T(K3)<>"000000", "Crossed to Port "&LEFT(K3,LEN(K3)-2)&" Ch "&RIGHT(K3,2)&" - "&VLOOKUP(VALUE(LEFT(K3,LEN(K3)-2)),'Y:\Main\Telcomm\SPPC Ckts\a). Indexes\[DSX.xls]GOB'!$F$2:$G$50028,2,0)&"  ("&VLOOKUP(VALUE(LEFT(K3,LEN(K3)-2)),'Y:\Main\Telcomm\SPPC Ckts\a). Indexes\[DSX.xls]GOB'!$F$2:$J$50028,3,0)&")",VLOOKUP(C3,'Y:\Main\Telcomm\SPPC Ckts\a). Indexes\[CIRUIT MASTER LIST.xls]Sheet1'!$A$2:$B$50002,2,0))),""

Alex Hedley
5 months ago
There's also the formula stepper, does it run through each function?
James Allen
5 months ago
I have used the fx box to show the outcomes of what Excel does with the formula and the "stepper" that walks you through the formula as you walk across it - The stepper jumps between the sub-functions in the formula and showing the terms, but when it gets too deep it sometimes adds to the confusion. I am trying to use formatting within the cell to separate terms and make the formula look more like a C or VBA function with the main pieces of the formula separated on separate lines not all smashed together. The alt-ent control does that BUT I was hoping for an indent or other control or  // comment  that may help further clarify a formula for the future reader- I am not trying to decipher the example I gave .. that was just to show the shear size of the formulas. I see I can help shrink the size by using names areas/cells but that is a little different from what i was looking for  . If there isn't any other way Thats fine ... I was trying to ask the Experts what they knew to help.
Alex Hedley
5 months ago
Could you not just make a UDF in VBA and just replicate the formula but add your own comments?

Or add a Note/Comment on the cell with an explanation
Or a notes tab with explanations
James Allen
5 months ago
Those are all good suggestions for work arounds... I am trying to make the formula in the cell itself more readable.. Thank you for the help... if I figure it out I'll let you know.
Richard Rost
5 months ago
Yeah super complex formulas in Excel are a pain. That's why I try to teach to break things up into multiple cells / steps.
James Allen
4 months ago
I understand "Helper" cells, and I even use hidden "Helper" Sheets in my workbooks filled with helper cells when necessary to keep eyes and fingers from the fragile innards of my Spreadsheet. I even found that you can perform functions vertically through a stack of sheets, copy/pasting/editing and calculations across them alike, very handy if you can keep it straight in your head.  Like  for example =SUM(FirstSht:LastSht!A1), sums A1 for all sheets between FirstSht and LastSht. You may have covered that?
Sorry I digress, the sheets I generally create are for equipment connection/details that have 10's of thousand of inter related data referred to in the cells, I can't manage a bunch of helper cells per cell at that level.. It does work beautifully as single celled formulas BUT I'm just trying to reformat these formulas using tools available for that in the cell to make reading and trouble shooting later easier. I use Alt-Enter but still is difficult to decipher sometimes.  
James Allen
4 months ago
Alex wrote earlier "Could you not just make a UDF in VBA and just replicate the formula but add your own comments?"

Can you point me to a video I can purchase on this?
My sheets work 100% without VBA now ,  but I may have to concede and add "code" to these in the future.....
May I can write a comment function/formatting  or something.

To date the client has not allowed Access for this work and insisted on plain vanilla Excel to track this data. Code and Macros trigger the Cyber controls in their systems
Alex Hedley
4 months ago
Are you not allowed .xlsm either then?
Richard Rost
4 months ago
Hi James. What you're talking about is called a Multi-Sheet Range. I covered that way back in Excel 230. It will be one of the topics I'll be getting to when I re-record my Excel series VERY soon. That plus I haven't done much with Excel VBA. My focus has been so much on Access over the past few years.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Excel Forum Comments

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/17/2022Fast AutoSum
2/22/2022ISO Date Format
1/27/2022Work Days
1/11/2022Excel Fast Tips
1/7/2022Count Cells by Color
1/7/2022Excel VBA
1/7/2022Excel Count by Color
12/26/2021Conditional Formatting
12/9/2021Excel 2003 Quick Start 1

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn