Pasting a Named range

E

Emile Zac

I have seven different ranges that have distinctive names. I am trying to
create a formula that will insert or paste the named range starting at the
active cell.
The formula would do the following:

The active cell is D70. If the value in cell E2 is one of seven text
strings ( red, green, blue .....) then insert the cell values of the
corresponding named range starting at D70.

In other words
Starting at the active cell
IF E2="red", then insert range named "Red_Cars" which refers to A100:B110
which contain information,
IF E2="blue", then insert range named "Blue_Cars" which refers to A120:B130
which contain information,
and so on.....


Any help would be appreciated

Thanks
 
M

Max

One way to try ..

Create a lookup table in say, G1:H2
with the entries:

red Red_Cars
blue Blue_Cars

Name the range G1:H2 as: TBL

Put in D70:

=OFFSET(INDIRECT(VLOOKUP(TRIM($E$2),TBL,2,0)),ROW(A1)-1,COLUMN(A1)-1,)

Array-enter the formula with CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Copy D70 across to E70, fill down to E80

The contents of the named ranges: Red_Cars, Blue_Cars
will be displayed in D70:E80
depending on the input in E2 (red, blue)

The named ranges: Red_Cars, Blue_Cars are assumed
identical in size (11R x 2C)
 
M

Max

Perhaps better with some error traps included in case
E2 is cleared, or for any unmatched inputs in E2 ..

Replace the *array* formula in D70 with:

=IF($E$2="","",IF(ISNA(VLOOKUP(TRIM($E$2),TBL,2,0)),"",OFFSET(INDIRECT(VLOOK
UP(TRIM($E$2),TBL,2,0)),ROW(A1)-1,COLUMN(A1)-1,)))
 
A

Arvi Laanemets

Hi

In Excel functions can't change aything, they only return values. P.e. when
you write a function, you can't use code which activates cells, or changes
cell format, or writes some value/formula into cell, etc. All those
operations are allowed only, when you write a procedure.

So or you use function(s) to display value(s) from your named range(s) in
cell(s), which contain this/those formula(s), accordingly parameters, or you
enter the parameter value into cell and start a procedure (by shortcut, or
by button, or by worksheets Change event), which inserts according values
into worksheet.
 
M

Max

Received OP's reply via email .. looks like it worked for him <g>
---
From: "Emile Zac"
To: "Max" <[email protected]>
Subject: Re: Pasting a Named range
Date: Thu, 21 Oct 2004 09:45:17 -0400

WOW! WOW!

Max:

I don't know who you are or why you do this, but I want to THANK YOU.

Your formula worked flawlessly, your saved me hours of trying to figure
the way to solve the problem.

Thank you again

Emile
 
Top