Excel Forum

Formula Management Upload Images Link
James Allen
4 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
4 months ago

fx: The formula bar also stretches to more than one line

James Allen
4 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
4 months ago

There's also the formula stepper, does it run through each function?

James Allen
4 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
4 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
4 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.
Thx

Richard Rost
4 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
3 months ago

Richard,
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
3 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
3 months ago

Are you not allowed .xlsm either then?

Richard Rost
3 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