Return a cell reference value in formula

[atp01]

Hi, i have no idea how to title it, i will just explain what i am trying ot achieve.

Cell A4 value is either a number or "N/A"
Cell A5 has a reference to cell A6 in Sheet 2 which has a value of 100
I would like to make A5 value change according to the value of A4
IF A4 is "N/A" then return "N/A"
IF A4 is number then A5 will return the value of A6 in sheet2

I use the following formula,
=IF(A4="N/A", "N/A", =Sheet2!$A$6)

And apparently this is not working.

Appreciate any helps and thank you in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

Try

=IF(ISNA(A4),A4,Sheet2!A6)

 

[etaf]

try
=IF(ISERROR(A4),"N/A",Sheet2!$A$6)

or if not an error - is it just text N/A

also have = will give an error in the formula

=IF(A4="N/A", "N/A", =Sheet2!$A$6)
change to
=IF(A4="N/A", "N/A", Sheet2!$A$6)

 

[atp01]

Welcome to the MrExcel board!

Try

=IF(ISNA(A4),A4,Sheet2!A6)

Sheet2!A6 this works, thank you very much.

 

[Peter_SSs]

You're welcome, glad to help.