Named Formula Error: INDIRECT Returns #VALUE Error Inside SUMIFS

[jpread]

Hello,

I am working on a large financial file and have noticed an error that keeps popping up. The error occurs when the formula below is used indirectly with a Formula Name (e.g. Test_Indirect). When used directly the formula works as intended, but if the formula name is called instead, the INDIRECT portion returns a #VALUE error. In the example below, G1 uses the formula name and does not work, G2 uses the actual formula and works.

The only difference I see is the contents of INDIRECT are enclosed in curly brackets when used in a formula name (see photos)

Example:

Formula Names:

Dates_Column_Letter="Sheet1!"&CHAR(64+COLUMN(Sheet1!$A:$A))
Next_Amount_Min_Row=ROW(Sheet1!$A$25)

Test_Indirect=SUMIFS($D:$D,$A:$A,">"&A2,$A:$A,"<="&INDIRECT(Dates_Column_Letter&Next_Amount_Min_Row),$B:$B,"Payment"))

G1 =Test_Indirect

G2 =SUMIFS($D:$D,$A:$A,">"&A2,$A:$A,"<="&INDIRECT(Dates_Column_Letter&Next_Amount_Min_Row),$B:$B,"Payment"))

 

[Special-K99]

You cant attach files on this forum.
See if you can edit your post and remove the unwanted info, it's cluttering up the thread and you may not get an answer if people can be bothered to scroll down far enough.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.