Is it possible to insert a range of cells depending on cell value,
e.g.
A1 contains "Sheet2!B1:D3"
result should be
A B C
2 B1 C1 D1
3 B2 C2 D2
4 B3 C3 D3

Hi
Array formulas are what you need.

On Sheet1 select The range needed off sheet2. In the activecell (a1) type =
and select the range on sheet2. Enter with Ctrl + Shift + Enter.

Note array formulas use more memory than normal formulas.

Regards
Peter

"firsttimer" wrote:

Is it possible to insert a range of cells depending on cell value,
e.g.
A1 contains "Sheet2!B1:D3"
result should be
A B C
2 B1 C1 D1
3 B2 C2 D2
4 B3 C3 D3

try this to get the values in the same lactation.

Sub getfromrange()
With Range("a1")
mloc = InStr(.Value, "!")
msheet = Left(.Value, mloc - 1)
mange = Right(.Value, Len(.Value) - mloc)
For Each c In Sheets(msheet).Range(mrange)
Range(c.Address) = c
Next c
End With
End Sub


--
Don Guillett
SalesAid Software

"firsttimer" wrote in message
...
Is it possible to insert a range of cells depending on cell value,
e.g.
A1 contains "Sheet2!B1:D3"
result should be
A B C
2 B1 C1 D1
3 B2 C2 D2
4 B3 C3 D3

Would an array formula be OK (entered with shift ctrl enter) be OK. In this
case simply select the required range, enter the formaula as you gave it and
hit shift+ctrl+enter ????

"firsttimer" wrote:

Is it possible to insert a range of cells depending on cell value,
e.g.
A1 contains "Sheet2!B1:D3"
result should be
A B C
2 B1 C1 D1
3 B2 C2 D2
4 B3 C3 D3

Ok, this works for an individual value,
How can I apply this to all vallues in a column.
e.g. sheet3 contains A1-A10:2,1,3,1,2,etc.
In sheet1 the wanted result should be
A B C
1 B1 C1 D1 (range2)
2 B2 C2 D2
3 B1 C1 D1 (range1)
4 B1 C1 D1 (range3)
5 B2 C2 D2
6 B3 C3 D3
7 B1 C1 D1 (range1)
8 B1 C1 D1 (range2)
6 B2 C2 D2

Thanks in advance

"Billy Liddel" wrote:

Hi
Array formulas are what you need.

On Sheet1 select The range needed off sheet2. In the activecell (a1) type =
and select the range on sheet2. Enter with Ctrl + Shift + Enter.

Note array formulas use more memory than normal formulas.

Regards
Peter

"firsttimer" wrote:

Is it possible to insert a range of cells depending on cell value,
e.g.
A1 contains "Sheet2!B1:D3"
result should be
A B C
2 B1 C1 D1
3 B2 C2 D2
4 B3 C3 D3