working with named ranges

[DavidObeid]

If I have 3 worksheets (sheet1, sheet2, sheet3) each with a range o
cells (1 column wide and varying rows long) named "range1", "range2
and "range3" respectively, can I write a formula to report the content
of the 3 ranges into column 1 of sheet4?

Thanks in advance,

Dav

 

[AH]

Hi Dave,

One way to do this (and not a very pretty, but a working one) is by
following formula:

=IF(ISERROR(INDEX(Range1,ROW())),IF(ISERROR(INDEX(Range2,ROW()-ROWS(Range1))
),IF(ISERROR(INDEX(Range3,ROW()-ROWS(Range2)-ROWS(Range1))),"",INDEX(Range3,
ROW()-ROWS(Range2)-ROWS(Range1))),INDEX(Range2,ROW()-ROWS(Range1))),INDEX(Ra
nge1,ROW()))

Copy it to as many cells in Sheet4 column A as needed starting from row 1.
If you want to start from some other row number, you should add a subtract
after every ROW() function in the formula. The amount of subtraction should
be the starting row - 1.

Hope you can make this work, it's a little complicated

- Asser