Getting a formula to lookup its own row

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? :(
 
A

AltaEgo

=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy addition formula of non-consecutive horizontal cells 2
Transfer data between two tables 3
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Cumulative formula for word table 0
Remove Identical words 0
Keyboard shortcut to select a column? 7
Convert old wordbasic code into VBA that will run now 0
Drop down list control value in vba 2

Top