Filling a cell with formula only if cell in the column to the left isfilled

[Raj]

Hi,

Is there a way other than VBA for filling a cell with a formula only
if the cell in the adjaced column is filled? The sheet is being used
as a database where new rows will be added periodically. Filling the
whole column with the formula makes the file size very large.

Thanks in Advance for all the help.

Regards,
Raj

 

[Bernie Deitrick]

Raj,

Hover your mouse pointer over the lowest cell with a formula's lower right corner (the fill square)
until your cursor changes from a white cross to a solid black cross, then double click. Excel will
fill down to match the filled column on the left or the right of that cell.

HTH,
Bernie
MS Excel MVP

 

[Raje]

Raj,

Hover your mouse pointer over the lowest cell with a formula's lower right corner (the fill square)
until your cursor changes from a white cross to a solid black cross, thendouble click. Excel will
fill down to match the filled column on the left or the right of that cell.

HTH,
Bernie
MS Excel MVP









- Show quoted text -

Thanks. But this was not what I was looking for.

Example:
Column A has values input by the user. Column B has a vookup formula
that looks up the value in Column A
Let's say that A5 and B5 are filled up.
When the user inputs the value in A6, the formula in B5 should get
automatically copied down to B6 without autofill or any other method.
If the formula is copied down the whole length of Column B the file
size becomes too large.

Thanks in Advance.

Regards,
Raj

 

[Bernie Deitrick]

Raj,

Ah.

Tools / Option.... Edit tab, check "Extend data range formats and formulas"

Another method would be to use the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target(0, 2).HasFormula And Not Target(1, 2).HasFormula Then
Application.EnableEvents = False
Target(0, 2).Copy Target(1, 2)
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

Raj,

Hover your mouse pointer over the lowest cell with a formula's lower right
corner (the fill square)
until your cursor changes from a white cross to a solid black cross, then
double click. Excel will
fill down to match the filled column on the left or the right of that
cell.

HTH,
Bernie
MS Excel MVP









- Show quoted text -

Thanks. But this was not what I was looking for.

Example:
Column A has values input by the user. Column B has a vookup formula
that looks up the value in Column A
Let's say that A5 and B5 are filled up.
When the user inputs the value in A6, the formula in B5 should get
automatically copied down to B6 without autofill or any other method.
If the formula is copied down the whole length of Column B the file
size becomes too large.

Thanks in Advance.

Regards,
Raj