IF & IF

[Jester 1978]

I am trying to set a formula where by the value in cell A1 is copied into A3 to A100.
But it will only show up if there is a value in B3 as well.

I can get it to work doing one or the other but not combined.

I have tried to use:

=IF($A$1=0,"",$A$1,IF(B3=0,"",$A$1))

Any help on this would be great as it is purely for cosmetic puposes on the spreadsheet, and it is driving me mad!!!!

 

[Juan Pablo González]

How about

=IF(LEN($A$1)*LEN(B3),$A$1,"")

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-09-19 06:51

 

[zzydhf]

how about: =IF(OR($A$1=0,B3 = 0),"",$A$1)

or: =IF(OR($A$1="",B3 = ""),"",$A$1)
_________________
Oh, you hate your job? Why didn't you say so? There's a support group
for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey
This message was edited by zzydhf on 2002-09-19 07:00

 

[paliman]

=IF($A$1=0,"",IF($B$3=0,"",$A$1))

Does it work?

 

[Jester 1978]

Thansk to both of you.

oh pretty spreadsheets!!!!!!!

 

[ChrisUK]

Hi Jester.

I don't understand what you're trying to do really.

If you want to copy A1 to cells, A3, A4, A5 ... A100 but only if cell B3 is not blank then I think you're going to have to result to a User defined function and VBA. If you just want to copy A1 to A3 if B3 is blank then try this:

=IF(ISBLANK(B3),A1,"")

I don't know a worksheet function which would let you zip through a whole range of cells, for this VBA would be much quicker

Here is a VBA sub

Sub copyit()

If Cells(3, 2).Value <> "" Then
For x = 1 To 10
Cells(3 + x, 1).Value = Cells(1, 1).Value
Next
End If

End Sub

Hope this helps you

 

[Jester 1978]

Thanks to both of you.

oh pretty spreadsheets!!!!!!!