Excel: Get Cell Adress of source Cell when content determined by if statement

i have a cell (lets call it A1) which uses an if statement to determine its content. i.E:

=IF(SheetName!H9="";SheetName!H$3;IF(SheetName!I9="";SheetName!I$3;IF(SheetName!J9="";SheetName!J$3;IF(SheetName!K9="<>";S开发者_如何学运维heetName!K$3;"stopped"))))

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

What you can do is display the address you want to refer to in a cell (let say B1) with almost the same formula as the one you already built:

=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"))))

This will return a string with the address of the cell.

Then, you can get the value of the cell with INDIRECT. For instance, you can put in A1:

=IF(B1="stopped","stopped",INDIRECT(B1))

I let you change the B1 with the needed $ depending on the reference you need.

There are functions like MATCH, which finds the position (row number from reference) of the desired content. Then you can transform it to show the address.

There is no native formula to get the address of the cell containing the formula.

I found the solution, which should probably function in the formulas in conditional formatting as well.

To get the address of q cell containing the formula you should type:

=ADDRESS(ROW(),COLUMN())