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$10 00<"",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))))
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

"KR" wrote in message ...
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$10 00<"",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))))
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

"KR" wrote:

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$10 00<"",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))))
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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 :-(

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

"KR" wrote:

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$10 00<"",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))))
--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

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


"David McRitchie" wrote in message
...
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

"KR" wrote in message
...
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$10 00<"",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))))
--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.