I'm trying to combine two cells and and array from a call to OFFSET()
as a single array to pass to IRR(). The two cells are in the current
sheet and the offset call references ranges and cells in a separate
sheet.

If I put everything in the same sheet and make the call
=IRR(($D$49,OFFSET($C$40:$AZ$47,1, 1,1,6),$D$50))
It works fine.

Also, if I make this call from a separate sheet it works:
=IRR((Flows!$D$49,OFFSET(Flows!$C$40:$AZ$47,1, 1,1,6),Flows!$D$50))

However, if I make the following call (where the value of C4 and
Flows!$D$49 are the same):
=IRR((C4,OFFSET(Flows!$C$40:$AZ$47,1, 1,1,6),Flows!$D$50))
it doesn't work. It returns a #VALUE! error. Seems like Excel doesn't
like me combining cells and results across sheets...

Does anyone how to make this work?

Thanks!