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

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.

--
Regards
Roger Govier



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

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

Alan

"Roger Govier" wrote:

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.

--
Regards
Roger Govier



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

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.
--
Regards
Roger Govier



"Alan" wrote in message
...
Thanks Roger,
Could you tell me how I indicate where the range should go from Sheet 2 to
Sheet 1?

Alan

"Roger Govier" wrote:

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.

--
Regards
Roger Govier



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