Okay, so that isn't the best title, so here is my dilemma:
I have the following formula in the current worksheet in cell K7, which brings in the row number for where the first instance of the date first occurs in the referenced file:
=IF(MATCH(A$2,'[July 2013 IDX Cash Final Post.xlsm]Prep for Pivot'!$I$1:$I$20000,0),MATCH(DATE(YEAR($A$3),MONTH($A$3),VALUE(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256))),'[July 2013 IDX Cash Final Post.xlsm]Prep for Pivot'!$D1:$D$20000,0),0)
This particular formula results in 75. So I now know that the first instance is in cell D75 in the referenced file. But in the next cell down I want to know where the 2nd instance is, then in the next cell, the 3rd instance, etc. What I want to occur is for "$D$1" (toward the end of the formula) to update based on the result of the cell above. So I want the formula in the next cell below the cell containing the formula resulting in "75" to be:
=SUM($K$7:$K7)+IF(MATCH(A$2,'[July 2013 IDX Cash Final Post.xlsm]Prep for Pivot'!$I$1:$I$20000,0),MATCH(DATE(YEAR($A$3),MONTH($A$3),VALUE(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256))),'[July 2013 IDX Cash Final Post.xlsm]Prep for Pivot'!$D76:$D$20000,0),0)
and yes, it has to be one row farther down than the previous result.
I attempted to do this by first breaking the formula up into pieces, so that I could isolate and change the cell reference, and then put it back together again with CONCATENATE. Then I tried using INDIRECT to change the text string that resulted back into a formula. But I keep getting a #REF! error even though the other file I am referencing is open. So then I tried, doing the concatenation within the INDIRECT function, but it gets hung up on the double quotes throughout the string.
Is there a simpler way to change the cell reference?
Or, if not, how do I get the Indirect function to work?
This has been driving me crazy!
Bookmarks