Hi there

I am trying to create a formula that looks in cell A1 in sheet B for a
matching value in A. When it finds the value in sheet A, I want it to copy
the next 10 cells in the corresponding row into cells B1, C1 etc...

Any advice appreciated.

Thanks

One way:

Select the 10 cell range B1:K1

Enter this array formula** :

=OFFSET(Sheet1!A1,MATCH(A1,Sheet1!A1:A10,0)-1,1,,10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"JaB" wrote in message
...
Hi there

I am trying to create a formula that looks in cell A1 in sheet B for a
matching value in A. When it finds the value in sheet A, I want it to
copy
the next 10 cells in the corresponding row into cells B1, C1 etc...

Any advice appreciated.

Thanks

One way

Sub copymatchingline()
For Each c In Range("b5:b6") 'destination range
With Sheets("sheet2")
ma = .Range("a2:a22").Find(c).Address
.Range(ma).Resize(1, 10).Copy c
End With
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JaB" wrote in message
...
Hi there

I am trying to create a formula that looks in cell A1 in sheet B for a
matching value in A. When it finds the value in sheet A, I want it to
copy
the next 10 cells in the corresponding row into cells B1, C1 etc...

Any advice appreciated.

Thanks