Insert Range Of Cells

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

Guest

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
 
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
 
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 ????
 
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
Click to expand...

Thanks in advance
 
Back
Top