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?

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:

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?

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

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

Copy down as far as you want.


Gord Dibben MS Excel MVP

On Wed, 7 Jan 2009 18:30:00 -0800, samus
wrote:

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?