Adress of "source" cell determined by if statement

[floshkit]

<table><tbody><tr><td class="votecell">
</td> <td class="postcell"> i have a cell (lets call it A1) which uses an if statement to determine its content. i.E:

<code>=IF(SheetName!H9="";SheetName!H$3;IF(SheetName!I9="";SheetName!I$3;IF(SheetName!J9="";SheetName!J$3;IF(SheetName!K9="<>";SheetName!K$3;"stopped"))))
</code> so depending on which statement turns out to be true, the cell A1 will be populated with different content.
so far so good.
now i need to find out the actual adress of the cell that A1 is pulling its contents from.
What function would i use to return the adress of the cell whos content is being displayed in A1?
i did my homework, looked, used the search function on here and many hours have been spent on not finding the answer i need. i found tons of other useful insights into excel, but this problem remains unsolved.
thank you very much


</td></tr></tbody></table>

 

[Andrew Poulsom]

Welcome to MrExcel.

Use your existing formula, changing eg SheetName!H$3 to CELL("address",SheetName!H$3).

 

[baitmaster]

welcome to the board

there is no specific function that does this exactly, but it is possible in various ways. You will need to duplicate the formula, to ask the same set of questions. Instead of returning the cell contents, you want it to return something else

1) put "" around the cell name. This is not good, because you are creating text, which won't update if you move the cell, you'll be getting the wrong abnswer in no time

2) replace the reference with ADDRESS(ROW(reference),COLUMN(reference)). Works, but is clumsy and difficult to read, especially with long formulas

3) create a VBA function addressA1 and replace reference with a new function called e.g. addressA1(reference)

In a standard VBA code module, place the following code:

Function addressA1(target As Range)
addressA1 = target.Address
End Function

EDIT
d'oh! forgot CELL...!
hangs head in shame for overcomplicating things...

 

[floshkit]

Tank you all.
VB will not work, since its to be collaborated on using google docs and google docs does not know how to handle macros. thank you for your answers. seems like its more involved than i had hoped it would be..