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

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


"Raj" wrote in message
...
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

On Apr 24, 7:48*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"Raj" wrote in message

...



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- Hide quoted text -

- 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

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

"Raje" wrote in message
...
On Apr 24, 7:48 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"Raj" wrote in message

...



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- Hide quoted text -

- 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