Returning a range using IF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Thanks Roger,
Could you tell me how I indicate where the range should go from Sheet 2 to
Sheet 1?

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