If Condition is True Then Paste Named Range

[bkg73123]

Hello,



Please help.
I have been searching all over and found some VBA examples, but I am not good with VBA.
I would prefer formula solution.


Example:
I have two named ranges on Sheet 2 called "Range1" & "Range2".
Sheet 1 Column A are the trigger cells.
If A1 has a value of 1 I need the named range "Range1" to automatically be pasted into cell B1.
Conversely, if A1 has a value of 2 I need the named range "Range2" to automatically be pasted into cell B1.

Thank you,
B

 

[skywriter]

You can't paste a range with a formula. What you can do is use an if array formula, but it only references the range.
Here I have two ranges the left set of numbers i DRange, the right is DRange2 you can see the array formulas and the result.

Excel 2010
	A	B	C	D	E
1	2	6
2		7
3		8
4		9
5		10
6
7
8
9
10				1	6
11				5	7
12				3	8
13				4	9
14				5	10
Sheet3

Cell Formulas
Range	Formula
B1:B5	{=IF($A$1=1,DRange,IF($A$1=2,DRange2,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DRange	=Sheet3!$D$10:$D$14
DRange2	=Sheet3!$E$10:$E$14