PDA

View Full Version : Solved: Cell Reference



maninjapan
04-29-2010, 09:04 AM
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
04-29-2010, 09:28 AM
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
04-29-2010, 09:33 AM
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
04-29-2010, 09:39 AM
How about this:

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

maninjapan
04-29-2010, 09:41 AM
thanks mbarron, was trying to use indirect......

mbarron
04-29-2010, 10:02 AM
Using Indirect:

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

maninjapan
04-29-2010, 10:03 AM
ok, so I could use either, thanks!!