Returning a range using IF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan - 11 Sep 2007 11:02 GMT

Does anyone know if it is possible to use a formular that would return a
selected range (from a different worksheet), based on the content of a given
cell?

I want to use the first worksheet as a form with the second worksheet
containing dropdrown lists and ranges. The situation I want to create is if
A1=AMH, the B1:B6 would be filled by a named range in the second worksheet.
is this possible?

Alan

Reply to this Message

Roger Govier - 11 Sep 2007 11:13 GMT

Hi Alan

One way
On sheet2, select cells B1:B6, then use following array formula to the
formula bar
{=INDIRECT(Sheet1!A1)}
To enter (or amend) an array formula, use Control, Sift Enter (CSE) not just
Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.

Signature

Regards
Roger Govier

> Does anyone know if it is possible to use a formular that would return a
> selected range (from a different worksheet), based on the content of a
[quoted text clipped - 9 lines]
>
> Alan

Reply to this Message

Alan - 12 Sep 2007 12:14 GMT

Thanks Roger,
Could you tell me how I indicate where the range should go from Sheet 2 to
Sheet 1?

Alan

> Hi Alan
>
[quoted text clipped - 20 lines]
> >
> > Alan

Reply to this Message

Roger Govier - 12 Sep 2007 23:08 GMT

Sorry Alan

Misread your question and thought you wanted the result on Sheet2.
With your named ranges on Sheet2, with AMH (or whatever) entered in cell A1
of Sheet1
select cells B1:B6, then enter the following array formula to the formula
bar
{=INDIRECT(A1)}

Use Control, Shift ,Enter as described before, and do not type the curly
braces yourself.

Signature

Regards
Roger Govier

> Thanks Roger,
> Could you tell me how I indicate where the range should go from Sheet 2 to
[quoted text clipped - 29 lines]
>> >
>> > Alan

Reply to this Message