Excel Indirect function with relative row numbers

[London 007]

I am not very good at Excel. I have a problem:

I have written the following formula in cell J5[TABLE="width: 50"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=IF(AND(INDIRECT("AF5")<0,INDIRECT("AF5")<INDIRECT("AJ5")),"N",IF(AND(INDIRECT("AF5")<0,INDIRECT("AF5")>INDIRECT("AJ5")),"W",""))
[/TD]
[/TR]
</tbody>[/TABLE]

However, when I copy the formula to cell J6 the formula remain the same!

After I have copied the formula to from J5 to J6 I would like the rows numbers to change automatically, i.e.:
=IF(AND(INDIRECT("AF6")<0,INDIRECT("AF6")<INDIRECT("AJ6")),"N",IF(AND(INDIRECT("AF6")<0,INDIRECT("AF6")>INDIRECT("AJ6")),"W",""))

Is this possible?

Thanks

James

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=IF(AND(INDIRECT("AF"&ROW(J5))<0,INDIRECT("AF"&ROW(J5))("AJ"&ROW(J5))),"N",IF(AND(INDIRECT("AF"&ROW(J5))<0,INDIRECT("AF"&ROW(J5))>INDIRECT("AJ"&ROW(J5))),"W",""))

 

[London 007]

Perfect. You are a star. Many thanks for your help.