Find Row Number Using ROW Function and CELL Function in Dynamic Formula

[rhratterman15]

Hello My Good Men,

I am having troubles with a formula.

I am trying to find the row number of a specific cell. This Formula is intended to be dynamic.

This is my entire formula: =INDIRECT("'"&$A$2&" "&$B$2&"'"&"!A"&ROW(CELL("address",INDEX(INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),MATCH(A4,INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),0)))),TRUE)

I have isolated the issue to the ROW portion:

=CELL("address",INDEX(INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),MATCH(A4,INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),0))) evaluates to >>> '[Book1]Payroll 04.17.2020'!$A$6

If I place that value ('[Book1]Payroll 04.17.2020'!$A$6) inside the ROW function ("=ROW('Payroll 04.17.2020'!$A$6)") it evaluates to >>> 6 as it should.

Then why is it when I nest one inside the other ("ROW(CELL("address",INDEX(INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),MATCH(A4,INDIRECT("'"&$A$2&" "&$B$2&"'!$A$6:$A$76"),0))))") I get an error?

Any help would be much appreciated.

Thank you in advance!

 

[Yongle]

link to a CLOSED workbook
works with Index ✔
but not with Index(Indirect( ....))

 

[rhratterman15]

link to a CLOSED workbook
works with Index ✔
but not with Index(Indirect( ....))

I don't think I understand? Could you be more specific?

 

[Yongle]

Could you be more specific?

Indirect cannot be used with a closed workbook

 

[rhratterman15]

Here, to simplify my question why doesn't this work?:

=ROW(CELL("address",A13))

Thank you for trying to help me with this.

 

[Yongle]

Both of these formulas return STRING "$A$13" not a reference $A$13
=CELL("ADDRESS",A13)
=ADDRESS(ROW(A13),COLUMN(A13))

Both formulas ....
=ROW(CELL("ADDRESS",A13))
=ROW(ADDRESS(ROW(A13),COLUMN(A13)))

... evaluate to ...
=ROW("$A$13")

BUT you require
=ROW($A$13)

Convert string "$A$13" to reference $A$13 by wrapping value returned (by =CELL or =ADDRESS ) inside INDIRECT
=ROW(INDIRECT(CELL("address",A13)))
=ROW(INDIRECT(ADDRESS(ROW(A13),COLUMN(A13))))