lookup issue....

[John]

I am stuck...cant figure out whats wrong with this formula....any help???

=if(isblank(Data Sheet!E3,"1",'Data Sheet'!E3))


Also if i want to use the above formula and copy to say 120 sheets but on
each sheet "E3" changes to the E4 E5 E6 etc....is there a way to do that
other than by hand? Thanks

 

[Jacob Skaria]

Try this

=IF(ISBLANK('Data Sheet'!E3),"1",'Data Sheet'!E3)

If this post helps click Yes

 

[Gord Dibben]

John

I don't think you want the "1" which will be text.

Better to use just =IF(ISBLANK('Data Sheet'!E3),1,'Data Sheet'!E3)

To increment this across 120 sheets run this macro.

Assumes Data Sheet is first sheet and you want the incremented formula in A1
of each sheet.

Sub Formula_Increment()
'increment a formula into A1 across sheets
Dim iCtr As Long
For iCtr = 2 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Formula = "=IF(ISBLANK('Data Sheet'!E" _
& iCtr + 1 & "),1,'Data Sheet'!E" & iCtr + 1 & ")"
End With
Next iCtr
End Sub


Gord Dibben MS Excel MVP