Formula Bar (2000)

Topic

New Reply

Anyone,
Sometimes when I need to insert a lengthy formula into a cell, the formula bar “spills over” (i. e., the formula takes up >1 line of text in the formula bar view) and hides cells I may need to get to. Any ideas other than simply de-activating the Formula Bar?
Thanks,
Jeff

Reply | Quote

Replies

Three ways round this that I can think of:

The first is, as you say to hide the formula bar,
the second is to split the screen either by Windows:Split or by dragging the little bar above the vertical scroll bar down,
The third is to increase screen resolution to a maimum possible. This won’t neccesarily make the problem go away but it might reduce the incidence of the problem.

Reply | Quote

Don’t think so-
No. 1-Don’t want to do this;
No. 2-I tried splitting, and the formula bar view “bleeds” over both splits, if I’m following you; and,
No. 3-Doesn’t cure the problem.
Thanks anyway,
Jeff

Reply | Quote

odd, splitting seems to work for me – admittedly the text isn’t that long, but you should be able to just move the split down.

Reply | Quote

Brooke,
I apologize-you are exactly right-I originally made a vertical split, as opposed to a horizontal one! Works fine now! Thanks.
One further refinement-what if I want to view one worksheet in one split frame, and another, different worksheet in the second frame? Or, one file in one split frame and another, completely different file in the other frame?
Thanks again,
Jeff

Reply | Quote

I think you’re after Windows:Arrange – have a play with that and see if you can get what you want.

Reply | Quote

Brooke,
Thanks again-it works fine with more than one file, but what about splitting to view two different worksheets in the same file/workbook? Even though I highlighted the box on the Windows:Arrange drop-down menu, it didn’t do anything!
Thanks,
Jeff

Reply | Quote

Sorry, I misread the question: what you want is (I think!) Windows:New Window which gives you a second window on the workbook and then Windows:Arrange to view them side by side.

Reply | Quote

Yep-works fine!
Thanks again.
Jeff

Reply | Quote

Another option: Do *NOT* have the workbook maximized, instead resize it so the top is away from the formula bar for enough.

Reply | Quote

Thanks, Jan, I’ll try it.
Jeff

Reply | Quote

Jeff

In addition to all the good examples that you have from the others, I suggest you work your formula in chuncks. So lets say you have a couple of nested Ifs, so you do the first in cell A1 eg, and then second if goes into cell A2 based on A1 and then the 3rd If goes in cell A3 based on cells A1 and A2 and so on. Once done and you know it works, combine all these cells into the cell you need to have the formula in.

also use flags to tell me where each cell goes something like:
In A1 I have =IF(CONCATENATE(Sheet2!F1,F1,Sheet3!F1)=”100″,”Stuff From A2″,”Stuff from A3″)
In A2 I have =IF(CONCATENATE(E1,Sheet2!E1,Sheet3!E1)=””,”Stuf from A4″,IF(ROUND(Sheet2!E2,2)>1,”Stuff from A5″,1/0))

HTH

Wassim

Reply | Quote

Wassim

I like to build complex formulas first in multiple cells as you suggest. But then I use the following macro to do my “nesting” for me.

I’ve used it on and off for a while and it works fine, but it hasn’t been fully tested, so it may break under some circumstances. I’m sure others can write more elegant code, but I’ve not noticed anything like this on the board. I’m sure others more expert could improve it and make it unbreakable.

Sub ReferenceReplace()
Dim RefMaster As Range
Dim RefServant As Range
Dim MasterFormula As String
Dim ServantFormula As String
Dim Work As String
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim x As Long
Dim y As Long
Dim NotFound As Boolean

On Error GoTo Cancelled

Set RefMaster = Application.InputBox("Select the cell containing the master formula", Type:=8)
Set RefServant = Application.InputBox("Select the cell containing the servant formula", Type:=8)

On Error GoTo 0

If RefMaster.Count  1 Or RefServant.Count  1 Then
    MsgBox "The master and servant references may only be one cell each. Procedure cancelled"
    Exit Sub
End If

ServantFormula = RefServant.Formula

'get rid of the equal sign in servant formula if it exists
'add quotes to unquoted text string
'do nothing to plain numbers
If Left(ServantFormula, 1) = "=" Then
    ServantFormula = Right(ServantFormula, Len(ServantFormula) - 1)
ElseIf IsNumeric(ServantFormula) Then
    'do nothing
Else
    ServantFormula = Chr(34) & ServantFormula & Chr(34)
End If


NotFound = True
For l = 1 To 4

Select Case l
    Case 1
        x = 1: y = 1
    Case 2
        x = 0: y = 1
    Case 3
        x = 1: y = 0
    Case 4
        x = 0: y = 0
End Select

Do
    MasterFormula = RefMaster.Formula
    'Debug.Print RefServant.Address(x, y)
    i = InStr(MasterFormula, RefServant.Address(x, y))
    If i > 0 Then
        NotFound = False
        j = i + Len(RefServant.Address(x, y))
        k = Len(MasterFormula) - j + 1
        Work = Left(MasterFormula, i - 1) & ServantFormula
        Work = Work & Mid(MasterFormula, j, k)
        RefMaster.Formula = Work
    End If
Loop Until i = 0
Next l

If NotFound Then
    MsgBox ("The servant formula reference was not found in the master formula")
End If

Cancelled:

End Sub

Ken

Reply | Quote