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

Excel VBA 1
Scrabble Value calculation for Welsh words 0
Custom functions 3
current row in formula 3
Formula Assistance 7
Formula Question 2
Excel EXCEL - using a date dependant formula to hide rows 3
Excel Help to copy information from one row at a time to another sheet 1

Top