title: Combine Excel Functions: Match & Offset
body: <p>I am trying to combine a couple of excel functions. The main functions are Match and Offset.
  I am trying to find a value (date) in AQ12 within another sheet in row  9). In this case it would return cell address G9. (Not $G$9 as this won't work in the offset function). From there I want to perform a SUMIF on G13:G100.</p>
  
  <p>What I have at the moment is:</p>
  
  <p><code>=ADDRESS(CELL("row",INDEX('Employee Resource'!G9:XFD9,,MATCH(AQ12,'Employee Resource'!G9:XFD9,0))),CELL("col",INDEX('Employee Resource'!G9:XFD9,,MATCH(AQ12,'Employee Resource'!G9:XFD9,0))),4)</code> </p>
  
  <p>which returns G9, which is fine as I know the sheet name. Let's call it "Employee Resource" for argument sake.  (Plus it is in the function above).</p>
  
  <p>I need to use that "G9" and the sheetname "Employee Resource" to produce something like:</p>
  
  <p><code>=COUNTIFS('Employee Resource'!G10:G100,$B$4)</code></p>
  
  <p>So I guess it would be something like
  <code>=COUNTIFS("'Employee Resource'!"&amp;"G10"&amp;":"&amp;"G100",$B$4)</code></p>
  
  <p>Where G10 and G100 are offsets of the original function
  G10 = OFFSET(...,0,1)
  G100 = OFFSET(...,0,91)
  But these return an error. (Not a #N/A or ERROR, but doesn't let me enter it.</p>
  
  <p>Does anyone know why? I have tried other mixtures such as...</p>
  
  <p>I believe I can use Offset to create a range
  G10:G100 = OFFSET(...,0,1,0,90)</p>
  
  <p><code>=OFFSET('Employee Resource'!G9,0,1)</code> returns a date value not address G10 which is what I would expect.</p>
  
  <p><code>=CELL("address",OFFSET('Employee Resource'!$G$9,91,0))</code> returns "'[New Production Schedule II.xlsm]Employee Resource'!$G$100" so includes the workbook name &amp; sheet &amp; cell but as an absolute reference.</p>
  
  <p><code>=CELL("address",OFFSET('Employee Resource'!$G$9,11,0))&amp;":"&amp;CELL("address",OFFSET('Employee Resource'!$G$9,91,0))</code> returns
  '[New Production Schedule II.xlsm]Employee Resource'!$G$20:'[New Production Schedule II.xlsm]Employee Resource'!$G$100
  When I add the SUM function it returns #VALUE!. If I do it via text without the function result it works fine albeit removing the workbook name. I think the issue is I need to use the result in the SUM function in this instance.</p>
  
  <p>Using the function INDIRECT should allow me to use the result but <code>=SUM(INDIRECT(CELL("address",OFFSET('Employee Resource'!$G$9,11,0))&amp;":"&amp;CELL("address",OFFSET('Employee Resource'!$G$9,91,0))))</code> returns #REF!</p>