Formula Bar (2000)

[jlkirk]

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

[Brooke]

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.

[jlkirk]

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

[Brooke]

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.

[jlkirk]

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

[Brooke]

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

[jlkirk]

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

[Brooke]

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.

[pieterse]

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

[jlkirk]

Yep-works fine!
Thanks again.
Jeff

[jlkirk]

Thanks, Jan, I'll try it.
Jeff

[Wassim]

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

[kjktoo]

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