Solved: Cell Reference

[maninjapan]

Im trying to write an if statement but not having much luck with the syntax, this is what I would like it to do.

=IF(Q20=1,D20,D20-value of Q20)

If the Value in Q20 is 4, then it will return the value in cell D16
If the value in Q20 is 10, it will return the value in D10

Any help on this would be much appreciated.

Thanks!!

[mbarron]

Based on this

If the Value in Q20 is 4, then it will return the value in cell D16
If the value in Q20 is 10, it will return the value in D10

=If(Q20=4,D16,if(Q20=10,D10,??value if not 4 or 10??))

This

=IF(Q20=1,D20,D20-value of Q20)

would be
=if(Q20=1,D20,D20-Q20)

[maninjapan]

sorry, might not have explained it clearly, the value could be anything (4 and 10 were just examples)
=if(Q20=1,D20,D20-Q20) D20-Q20 will just return a value, I want the row number to change based on the value in Q20

D(20-Q20) If Q20 value is 4 then it will reference D16,
If Q20 value is 5 then it will reference D15
If Q20 value is 6 then it will reference D14 etc......

[mbarron]

How about this:

=IF(Q20=1,D20,OFFSET(D20,-Q20,0))

[maninjapan]

thanks mbarron, was trying to use indirect......

[mbarron]

Using Indirect:

=IF(Q20=1,D20,INDIRECT("D"&20-Q20))

[maninjapan]

ok, so I could use either, thanks!!