VIew whole formula. Across next cells too... 1

[Welshbird]

This is just one of those annoying things I've never understood....

I have some fairly long formulas in a spreadsheet classic example below:

=IF($H3=0,0,$H3/IF($B$4="Monthly",
SUMIFS(data,Mnth,DATE(YEAR($B$5),MONTH($B$5)-12,1),Group,$B$2,Type,$B$3,PCO,$D3),
IF($B$4="MQT",
SUMIFS(data,Mnth,">="&DATE(YEAR($B$5),MONTH($B$5)-14,1),Mnth,"<="&DATE(YEAR($B$5),MONTH($B$5)-12,1),
Group,$B$2,Type,$B$3,PCO,$D3),0)))

These have forced CRLF (from pressing [alt] & [enter]) to make them easier for me to see.

However, or some sheets these display acorss (and in effect OVER the adjascent (sp?) cells, and in some cases they don't. The next cell is never empty, and I can't work out how and indeed why that happens.

It means that on some sheets I can easiely see the whole formula just by pressing [F2], and sometimes I can as it tries to display in only the width of the cell...

Any thoughts woul;d certainly salve my annoyance with this!

Cheers chaps.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[GlennUK]

Fee,

why don't you change the height of your formula bar when you are looking at a long formula?

I have mine set to about 4 lines in height for long formulas ... I toggle between normal and expanded formula bar by pressing Ctrl-Shift-U ( Excel 2007 ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

You might put the SUMIFS() in separate adjacent columns and just reference those cells in this formula. It will greatly simplify the maintenance and understandablilty.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

HI Skip,

I did think of that, but similar formula's exist in about 36 columns (ish...) and about 200 rows in 7 different worksheets, so it begins to get a bit ungainly when I do that.

La de da!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

THIS is a static value for all formulae
[tt]
DATE(YEAR($B$5),MONTH($B$5)-12,1)
[/tt]
Certainly THAT, and the similar on(s???) could be calculated in ONE CELL and so referenced.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

What I'm really trying to understand though is why sometimes my formula looks like this:

and on other sheets displayes like this:

What have I done to cause the second-type view? I've tried to google - but I'm not even sure how to describe what is happening.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

no pix

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Pic ONE: (as I'd love them all to be!)
[URL unfurl="true"]http://flic.kr/p/afBsYc[/url]
Pic TWO: (as they unfortunately are....)
[URL unfurl="true"]http://flic.kr/p/afBsYV[/url]

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

This is not an answer to your question, but it might solve your problem.

If you are wanting to see the formulae for the purposes of getting your head round how it all hangs together and check the cells' relationships, why not (temporarily if you like) include an extra column in which you insert a function to display the formula in another cell, then point the function at the cells you want to examine?

That way you can show all your formulae simultaneously and ensure they are all visible by autosizing the cells in which the formulae are shown.

I use:

Public Function AL_Formula(tgt As Range) As String
'
'  1. DESCRIPTION of AL_Formula
'
'   This function returns the formula in the selected cell as text.
'
'***************************************************************

  AL_Formula = tgt.Formula

End Function

Does that help?

Tony

 

[Welshbird]

I might... but its such a regular thing, and my instinct is there MUSt surely be a way to control how the formula is displayed in tersm of 'stay column width' or 'let people see you'.

It's just a real annoyance. there are certainly wys around it - I tend to copy and paste to Notepad pften; but it would surely be quicker to know how to just change it!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[GlennUK]

Fee, on the second-type view ... is the cell near the right-hand edge of the screen?

I don't have Edit Directly In Cell ticked ( Options ), so I don't see this kind of problem ... I only edit in the Formula bar. Hence my earlier suggestions.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Welshbird]

I see what you mean. I actually hide the formula bar when I provide to the end user, as it only confuddles them!

I could certainly make my formula bar higher; its purely just one of those things that I don't uderstand the reason why it happens, and that just makes me frustrated!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

The other thing is; This happens in ONE workbook, so the options are surely the same for all sheets? This isn't at the right-hand side of the screen either.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Gruuuu]

Excel only does this when there are contents in to the cell to the right. I don't really know a way around it other than not editing formulas in the cells, sticking with the formula bar.

 

[Welshbird]

In both examples there are formulas in the cells to the right - and they both equate to zero!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

You can temporarily DELETE the contents to the right in order to view the formula in the cell.

Also check out the EVALUATE FORMUAL feature. At the very least you can VIEW the formula in the Evaluate Formula Window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

I'm going to go with the extended formula bar and the crlt shft & U option for now.

But gotta be hiones -I find it really annoying!


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[lionelhill]

Big thanks to Glenn for ctrl-shift-U.

By the way, does Microsoft have a special department dedicated to hiding useful features in Excel? And who comes up with the wonderful key-combinations? If you press Alt-Tab-@ while tapping the "5" key on the number-pad with your nose, does Excel tell you the time in Peru?