Ty,

The difficulty is in balancing the parentheses. The best way is to use VBA.

Copy the macro below, place it in a codemodule, select the cells with the
formulas to be modified, then run the macro.

HTH,
Bernie
MS Excel MVP

Sub FixTysFormulas()
Dim myCell As Range
Dim myForm As String

For Each myCell In Selection
myForm = myCell.Formula
myForm = Replace(myForm, "=", _
"=IF(Z" & myCell.Row & "1,"" "",", 1, 1) & ")"
myCell.Formula = myForm
Next myCell
End Sub


"Ty Williams" wrote in message
...
Hi,

Perhaps it is easier to explain what I am trying to do...


IF('New Look Nil Reports'!B43=1,"NIL","NON")

Basically the above is how it is at present, checking if a sheet has a
one in B43.

I need to do the following

=+IF(Z7=1," ",IF('New Look Nil Reports'!B43=1,"NIL","NON"))

Whereas the Z7 relates to the cell to the left; this will then show a
blank (a space actually) in the cell, rather than the NIL or NON
label.

My problem is that the B43 section is not sequential, so I cannot use
a fill; the next line may be B12 or B65.

So I will need to find and replace

+IF(
with
+IF(LEFTCELL=1,"",IF(

Where LEFTCELL relates to the command/function to check the cell to
the left.

and I will need to add a close parenthesis at the end.....

Otherwise I will have to enter it all manually, and CRTL/SHIFT+ENTER
for the curly brackets as it is a array...

I am learning slowly, but this eludes me..

Many thanks

Ty