referencing the value of a cell containing an array formula

[KR]

As a followup to my last post...

I now have a group of contiguous cells on a background worksheet that
contain information I want to put on my primary worksheet (a report). The
cells contain an array formula.

I go to my main worksheet, and do the usual "=Sheet3!H4" and I get a value
of zero instead of what is showing in that cell. I've also tried variations,
including "=Value(Sheet3!H4)" and even entering it as an array formula
itself ({=Sheet3!H4}).

I'm only getting a "0" in my target cell(s), and I'm not sure what I'm doing
wrong... I also tried sticking the original array formula in my ultimate
destination cells, but I still get a "0" (possibly because my source range
is on a different worksheet?)

Using Win2000, XL2003.

Thanks in advance,
Keith

Array formula, with credit to N Harkawat, works fine on the same sheet as
the source data:
=IF(ISERROR(INDEX($H$1:$H$1000,SMALL(IF($H$1:$H$1000<>"",ROW($H$1:$H$1000)),
ROW(2:2)))),"",INDEX($H$1:$H$1000,SMALL(IF($H$1:$H$1000<>"",ROW($H$1:$H$1000
)),ROW(2:2))))

 

[David McRitchie]

You can't assign a value of Empty with a formula, so you would
have to have your formula show an empty string.

=IF(ISEmpty(Sheet3!h4),"",Sheet3!h4)

 

[bj]

Check the cell format on your primary sheet in that cell. make sure it is
general

 

[KR]

It is; and I'm able to direct reference cells on my background worksheet
that aren't in the array formula, e.g., =Sheet3!A4 gives me the actual text
in that cell. It just doesn't want to pull the array formula results for
some reason :-(

 

[KR]

David-

Thanks for your reply; please allow me to clarify- I can reference
"non-array formula cells" on a background worksheet, but I need to grab the
results from a range on that background sheet that is populated via an array
formula, and show those in a range on my main worksheet. When I reference
regular cells (straight text, or basic formulas) I can get the value from
the background sheet, but when I reference a cell that is populated by an
array formula, I don't get that value on my main worksheet...

You can't assign a value of Empty with a formula, so you would
have to have your formula show an empty string.

=IF(ISEmpty(Sheet3!h4),"",Sheet3!h4)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

As a followup to my last post...

I now have a group of contiguous cells on a background worksheet that
contain information I want to put on my primary worksheet (a report). The
cells contain an array formula.

I go to my main worksheet, and do the usual "=Sheet3!H4" and I get a value
of zero instead of what is showing in that cell. I've also tried variations,
including "=Value(Sheet3!H4)" and even entering it as an array formula
itself ({=Sheet3!H4}).

I'm only getting a "0" in my target cell(s), and I'm not sure what I'm doing
wrong... I also tried sticking the original array formula in my ultimate
destination cells, but I still get a "0" (possibly because my source range
is on a different worksheet?)

Using Win2000, XL2003.

Thanks in advance,
Keith

Array formula, with credit to N Harkawat, works fine on the same sheet as
the source data:

)),ROW(2:2))))