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.
What I have at the moment is:
=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)
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).
I need to use that "G9" and the sheetname "Employee Resource" to produce something like:
=COUNTIFS('Employee Resource'!G10:G100,$B$4)
So I guess it would be something like
=COUNTIFS("'Employee Resource'!"&"G10"&":"&"G100",$B$4)
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.
Does anyone know why? I have tried other mixtures such as...
I believe I can use Offset to create a range G10:G100 = OFFSET(...,0,1,0,90)
=OFFSET('Employee Resource'!G9,0,1) returns a date value not address G10 which is what I would expect.
=CELL("address",OFFSET('Employee Resource'!$G$9,91,0)) returns "'[New Production Schedule II.xlsm]Employee Resource'!$G$100" so includes the workbook name & sheet & cell but as an absolute reference.
=CELL("address",OFFSET('Employee Resource'!$G$9,11,0))&":"&CELL("address",OFFSET('Employee Resource'!$G$9,91,0)) 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.
Using the function INDIRECT should allow me to use the result but =SUM(INDIRECT(CELL("address",OFFSET('Employee Resource'!$G$9,11,0))&":"&CELL("address",OFFSET('Employee Resource'!$G$9,91,0)))) returns #REF!
