Getting a formula to lookup its own row

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

I am trying to get a formula that people can copy from a formula sheet into a
spreadsheet that will automatically look up the row that it is sitting in.
The hard part is this is part of a conditional formula and the regular =
ROW() does not seem to be working. What I want to do is:

=IF($F(current row)=1,G(current row),0)

In other words, if a trigger in a cell is set at "1", make this cell the
total in the G column of the current row, or if it isn't "1" make it 0.

It's doing my head in!
Any ideas? :(
 
=IF(INDIRECT("F"&ROW())=1,INDIRECT("G"&ROW()),0)


Breaking this down:

= Row() returns the current row
= "$F" & ROW() concatenates $F and the value returned by the ROW() function
(E.G. if row 6 the value returned is $F6)
=INDIRECT("$F"&ROW()) tells Excel to return the value from the address
specified in the INDIRECT parameter.
 
Back
Top