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

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!

combining cells and array from different sheets into an array to p
 

=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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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!

combining cells and array from different sheets into an array to p
 

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??

Max wrote:
=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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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!

combining cells and array from different sheets into an array
 

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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??