How can Excell automatically fill in a default function for a colu

[Richard-44]

I can't find this feature defined/explained anywhere, but I admit I'm having
a hard time knowing what search-phrase to use. Automatic Fill? Automatic
Input? Inputing Default Formula(s/e)? Anyway, here's the description of the
feature I need help with:

In some circumstances, when I type input into a column in a new row, Excel
will, in another column, autmatically place the formula that this other
column uses in other rows.

In sometimes the same circumstances, Excel doesn't do this, forcing me,
whenever I add/create a new row, to go to each column that has a default
formula and bring down that formula from previous rows using the manual
facilites (i.e. fill-down/copy/manual-input/etc.)

Specifically, I have a column where I provide the date. For sorting and
filtering reasons, I want to have a separate column for the weekday for that
date, so instead of formating the date-cell to include the weekday, I put the
formula: =TEXT(F2,"ddd") in the next column to the right (the G-Column). If
I go to the bottom of the spreadsheet and type the date into an F-Column cell
of a new row, Excel automatically adds the fomula =TEXT(F2,"ddd") into the
G-Column cell of the same new row. (If I choose "Insert>Row" and add the
date, however, Excel does not autmatically input the formula.)

I cannot seem to re-create/control this facility with any other
formula/row/column/cell.

Does this feature have a name that I could use to look-up in Help? Is it
controlled by some "Option>[Choice]" protocol? Is it triggered by how the
row/column-formula was created? Is it triggered by layout, such as one
column's adjacency to another? Or are there some functions (e.g. TIME
functions vs. FINANCIAL functions) that have this feature?

Anything anyone can tell me about this feature would help, because it would
be great to have more than one column automatically assume a formula of my
choosing every time I add a row.

Thanks!

 

[Bob Phillips]

see response in .misc

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

The feature you describe is available under Tools | Options | Edit tab
and is called Extend List Formats and Formulas in XL2000 (I think the
wording changed in later versions).

However, I have found the same inconsistencies as you, and I've not
worked out the logic of this facility yet. I was unable to find a
reference in Excel Help that describes it.

Hope this helps.

Pete

 

[Spiky]

I can't find this feature defined/explained anywhere, but I admit I'm having
a hard time knowing what search-phrase to use. Automatic Fill? Automatic
Input? Inputing Default Formula(s/e)? Anyway, here's the description of the
feature I need help with:

In some circumstances, when I type input into a column in a new row, Excel
will, in another column, autmatically place the formula that this other
column uses in other rows.

In sometimes the same circumstances, Excel doesn't do this, forcing me,
whenever I add/create a new row, to go to each column that has a default
formula and bring down that formula from previous rows using the manual
facilites (i.e. fill-down/copy/manual-input/etc.)

Specifically, I have a column where I provide the date. For sorting and
filtering reasons, I want to have a separate column for the weekday for that
date, so instead of formating the date-cell to include the weekday, I put the
formula: =TEXT(F2,"ddd") in the next column to the right (the G-Column). If
I go to the bottom of the spreadsheet and type the date into an F-Column cell
of a new row, Excel automatically adds the fomula =TEXT(F2,"ddd") into the
G-Column cell of the same new row. (If I choose "Insert>Row" and add the
date, however, Excel does not autmatically input the formula.)

I cannot seem to re-create/control this facility with any other
formula/row/column/cell.

Does this feature have a name that I could use to look-up in Help? Is it
controlled by some "Option>[Choice]" protocol? Is it triggered by how the
row/column-formula was created? Is it triggered by layout, such as one
column's adjacency to another? Or are there some functions (e.g. TIME
functions vs. FINANCIAL functions) that have this feature?

Anything anyone can tell me about this feature would help, because it would
be great to have more than one column automatically assume a formula of my
choosing every time I add a row.

Thanks!

It definitely relies on the data being a solid block without blank
columns/rows. And some of the manually entered columns have to be
numbers, I believe. It's usually when you type in a number that is
used in the formula columns that the formulas are auto-filled-in. Not
sure offhand if dates are good enough, I seldom enter dates like this.

I find CTRL-D (Fill Down) and CTRL-SHIFT-Arrows to be very quick when
the autofill fails. Enter all my new data, move to the formula columns
with Right Arrow, CTRL-SHIFT-UP to select all the blanks I need
filled plus the last existing formula, hit CTRL-D. Can do all columns
at once if you have several. Works just as fast if you enter 1 new
item or 1000. I can do this in seconds with my eyes closed, now.