Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula Bar (2000)

    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

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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.
    Attached Images Attached Images

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

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

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    Another option: Do *NOT* have the workbook maximized, instead resize it so the top is away from the formula bar for enough.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    Yep-works fine!
    Thanks again.
    Jeff

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    Thanks, Jan, I'll try it.
    Jeff

  12. #12
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,437
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formula Bar (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> 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 <font color=red> =IF(CONCATENATE(Sheet2!F1,F1,Sheet3!F1)="100","Stu ff From A2","Stuff from A3") </font color=red>
    In A2 I have <font color=red>=IF(CONCATENATE(E1,Sheet2!E1,Sheet3!E1)= "","Stuf from A4",IF(ROUND(Sheet2!E2,2)>1,"Stuff from A5",1/0)) </font color=red>

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  13. #13
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar (2000)

    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.


    <pre>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
    </pre>


    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •