rhratterman15
New Member
- Joined
- May 11, 2020
- Messages
- 16
- Office Version
- 365
- 2019
- 2016
- 2013
- Platform
- Windows
- Mobile
- Web
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!
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!