Using Cell Address in formula

[Kpbeard]

I am using this formula, =@CELL("address",INDEX(A$14:A$3000,MATCH(B10,A$14:A$3000,1))), to find the cell address for B10, which is a date on sheet 1 of my workbook. This formula results in $A$2364 on sheet1. On sheet2 I would like to use the result of the CELL formula, $A$2364 in a formula that is using SUMIFS back on Sheet1!$A:$A as part of the complete formula. I would like to replace the Sheet!$A:$A with Sheet!$A$2364 but have not found a way to do this yet. If I can find a way to do this, then the rest of the formula will work out as I have several variables using the same method described above. Any help would be great appreciated and I'm working on a general spreadsheet example to help explain my problem.

 

[Kpbeard]

Now the next question in my quest. The formula, =INDIRECT("Sheet1!A10"), returns a cell reference, $A$14. How can I use this result in a sumif formula? Example that I'm trying to resolve, =SUMIF(Sheet1!$B:$B,A5, Sheet1!$A$14:$A$3000). Whereas, A5 has the contents from =INDIRECT("Sheet1!A10").

 

[Kpbeard]

Correction in my formula. =SUMIF(Sheet1!A5:A6, B7, Sheet1!$B$14:$B$120) on Sheet2.

Whereas, A5:A6 return the values of $A$14:$A$120 based on Sheet2 A5 as the =INDIRECT("Sheet1!A10") & Sheet2 A6 has the =INDIRECT("Sheet1!A11")

 

[MARK858]

You really are much better off creating a named range rather than trying to use multiple INDIRECT statements