Help with creating a function

[Craig]

Hi,

I need help with this function.

=IF(D2="text", "G2","0")

When I copy the formula to the cells below it changes the reference of the
logical test, which is great, but I also need it to change the reference cell
of the true result. So that when I copy it to the cell below it becomes:

=IF(D3="text", "G3","0")

Thanks,

Craig

 

[JE McGimpsey]

Are you trying to return the *value* that is in cel G3, G4, etc?

Then

=IF(D3="text", G3, 0)

If you're trying to return the text strings "G3", "G4", etc., and the
text string "0", then, assuming your formula is in row 3:

=IF(D3="text", "G" & ROW(), "0")

If it's in a different row, add or subtract a value to suit. For
instance, if the formula is in row 2:

=IF(D3="text", "G" & ROW()+1, "0")

 

[David Biddulph]

You haven't given a reference cell of the true result, you have given a text
string "G2".
If you wish G2 to be a cell reference, it doesn't have quotes around it. In
that case it will happily update as you copy to the row below.
Similarly, if you want the alternative result to be number zero, rather than
a text string, you don't want the quotes round that either.

 

[Lars-Åke Aspelin]

Hi,

I need help with this function.

=IF(D2="text", "G2","0")

When I copy the formula to the cells below it changes the reference of the
logical test, which is great, but I also need it to change the reference cell
of the true result. So that when I copy it to the cell below it becomes:

=IF(D3="text", "G3","0")

Thanks,

Craig

Try this:

=IF(D2="text","G"&ROW(G2),"0")

Hope this helps / Lars-Åke

 

[Craig]

Thanks, that was very helpful. I removed the quotes around the true result
and removed the quotes in the false result and it updates the function
perfectly. I ended up with this:

=IF(D2="text", G2,"")

Craig