Insert Range Of Cells

[firsttimer]

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

 

[Billy Liddel]

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

 

[Don Guillett]

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

 

[Ben McBen]

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]

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