automatic formula

[samus]

Is there anyway to format a worksheet to have the same formula used in every
cell, with the only difference being a different row number? For example, I
have a worksheet that I input orders for clients on, called the "order log,"
and I'm trying to make it as efficient as possible. On another worksheet, I
have a list of the client numbers in column A and the client names in column
B. On the Order Log, I want to type in the client number and have it
automatically populate the Client Name in the cell right next to it. I've
tried the lookup formula, which works, but I have to drag the formula down
EVERY time. So is there anyway to make it so whenever I type the client
number in, let's say, column 4, in column 5 the client name associated with
that number automatically populates?

 

[Stefi]

If you have your lookup formula in the last filled row then this change event
sub shall fill down it to the current row:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then _
Cells(Target.Row - 1, Target.Column + 1).AutoFill _
Destination:=Range(Cells(Target.Row - 1, Target.Column + 1),
Cells(Target.Row, Target.Column + 1)), Type:=xlFillDefault
End Sub

Post if you need help to install it!

Regards,
Stefi


„samus†ezt írta:

 

[Gord Dibben]

Add an error trap to the formulas so if no data, the cells look blank.

=IF(ISNA(VLOOKUP(D1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(D1,Sheet2!A:B,2,FALSE))

Copy down as far as you want.


Gord Dibben MS Excel MVP