Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting a formula to lookup its own row
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? :( |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting a formula to lookup its own row
=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. -- Steve "Angela" wrote in message ... 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? :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using lookup formula | Excel Worksheet Functions | |||
LOOKUP formula | Excel Worksheet Functions | |||
LOOKUP Formula | Excel Worksheet Functions | |||
Lookup Formula Help - PLEASE! | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |