Re: referencing the value of a cell containing an array formula

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" <nospam@xxxxxxxxxx> wrote in message news:O0n19dXgFHA.2880@xxxxxxxxxxxxxxxxxxxxxxx
> As a followup to my last post...
>
> I now have a group of contiguous cells on a background work*** that
> contain information I want to put on my primary work*** (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 work***?)
>
> Using Win2000, XL2003.
>
> Thanks in advance,
> Keith
>
> Array formula, with credit to N Harkawat, works fine on the same *** 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))))
> --
> 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.
>
>


.