combining cells and array from different sheets into an array to pass to IRR()

[danyates77]

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!

 

[Guest]

=IRR((C4,OFFSET(Flows!$C$40:$AZ$47,1, 1,1,6),Flows!$D$50))

Untested, but think we could try it with INDIRECT wrapped around C4:
=IRR((INDIRECT(C4),OFFSET(Flows!$C$40:$AZ$47,1, 1,1,6),Flows!$D$50))
where C4 contains the text: Flows!$D$49

 

[danyates77]

Hmmm.. that doesn't work for me. I can't have C4 contain the text
"Flows!$D$49", because I'd essentially be replicating the "Flows" sheet
in the current sheet (there are many cells that I'm trying to do this
for).

The call I need to make is something like:
=IRR((C4, OFFSET(Flows!$C40:$AZ$47,1,1,1,6),Flows!$D$50))

Trying:
=IRR((INDIRECT("C4"), OFFSET(Flows!$C40:$AZ$47,1,1,1,6),Flows!$D$50))
doesn't work either...

any other suggestions??

 

[Guest]

Sorry, pl dismiss the earlier suggestion.

Ref your orig. post, I wasn't even able to get either
of the expressions below that you mentioned working:
=IRR(($D$49,OFFSET($C$40:$AZ$47,1,1,1,6),$D$50))
=IRR((Flows!$D$49,OFFSET(Flows!$C$40:$AZ$47,1,1,1,6),Flows!$D$50))

Hang around for better insights from others ..