Problems with slope indirect address

[jaschen27]

I'm running into an issue with using slope indirect when referencing addresses:

This formula works fine:

=SLOPE(INDIRECT(E39&":"&F39),INDIRECT(E40&":"&F40))

where:
E39 = '[Credit Tranche Analysis.xlsm]HY'!$C$1004
F39 = $C:$1004
E40 = '[Credit Tranche Analysis.xlsm]HY'!$I$1004
F40 = $I$1256

However when I substitute in the formulas for the respective cell values, E39, F39, E40 and F40, I end up with an N/A error. Specifically, substituting in F39 and F40 cause the formula to break.

Formulas in those cells are:
E39 = CELL("address",OFFSET(INDEX(HY!$A$3:$AQ$1400,MATCH(Sheet6!$B$2,HY!$A$3:$A$1400,0),MATCH(Sheet6!$B4,HY!$A$3:$AQ$3,0)),-252,0))
F39 = ADDRESS(ROW(INDEX(HY!$A$3:$AQ$1400,MATCH(Sheet6!$B$2,HY!$A$3:$A$1400,0),MATCH(Sheet6!$B4,HY!$A$3:$AQ$3,0))),COLUMN(INDEX(HY!$A$3:$AQ$1400,MATCH(Sheet6!$B$2,HY!$A$3:$A$1400,0),MATCH(Sheet6!$B4,HY!$A$3:$AQ$3,0))))

[Kaper]

E39 = '[Credit Tranche Analysis.xlsm]HY'!$C$1004
F39 = $C:$1004
E40 = '[Credit Tranche Analysis.xlsm]HY'!$I$1004
F40 = $I$1256

Sounds, you try to find slope for different length X and Y ranges:
'[Credit Tranche Analysis.xlsm]HY'!$C$1004:$C$1004 (1 row)
vs '[Credit Tranche Analysis.xlsm]HY'!$I$1004:$I$1256 (253 rows).

[jaschen27]

Sorry, that was a typo in the initial post:

F39 = $C:$1256

The problem still stands when using this slope indirect address function, that substituting the address function directly into the formula causes #N/A

[Kaper]

it's probably typo again. shall be: $C$1256

Anyway, probably easiest way to check what happens is by examining sample file.

So if you could attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.