formulas without copying?

[james]

I¹m trying to have my formulas flow down the page for each column.
Without manually copying them down the page I thought I could use the
³list² feature. Thus hopefully only having the list move down the page
as data is added. Sounds ok?

But when I actually insert a list over the data and formulas- it looks
great Except that when you goto add a new line of data the formulas
aren¹t there? Going into list wizard and changing the settings for each
column seems very longwinded and seemingly not possible to even add the
formulas correctly for each column. Ah..

I would have thought that by changing into List that the formulas for
each cell would simply follow down . .

Is there another way or should i be now using a database program instead?

Thanks.

 

[J Laroche]

james wrote on 2005/03/05 19:18:

I¹m trying to have my formulas flow down the page for each column.
Without manually copying them down the page I thought I could use the
³list² feature. Thus hopefully only having the list move down the page
as data is added. Sounds ok?

But when I actually insert a list over the data and formulas- it looks
great Except that when you goto add a new line of data the formulas
aren¹t there? Going into list wizard and changing the settings for each
column seems very longwinded and seemingly not possible to even add the
formulas correctly for each column. Ah..

I would have thought that by changing into List that the formulas for
each cell would simply follow down . .

Is there another way or should i be now using a database program instead?

Thanks.

Do you mean that for example cell C1 has formula A1 + B1, and you want that
C2 has A2 + B2, C3 has A3 + B3, etc? If so, all you have to do is put the
mouse pointer over the bottom right corner of the cell, where it will change
shape. Drag down (or in any other direction) as many cells as you wish.

Another way is to select the source cell and the entire destination range
(which can be non-contiguous to the source cell, by holding the Apple key),
then choose Fill from the Edit menu.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[james]

james wrote on 2005/03/05 19:18:


Do you mean that for example cell C1 has formula A1 + B1, and you want that
C2 has A2 + B2, C3 has A3 + B3, etc? If so, all you have to do is put the
mouse pointer over the bottom right corner of the cell, where it will change
shape. Drag down (or in any other direction) as many cells as you wish.

Another way is to select the source cell and the entire destination range
(which can be non-contiguous to the source cell, by holding the Apple key),
then choose Fill from the Edit menu.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

Thanks for your reply JL,

I have a set of cells on the same row which all contain formulas.
Data is entered every month on a new row. Although i realise i could
fill the cells (containing the formula) downward to a point at which i
predict the data-entry to continue till - it seems messy. Also i have
100 sheets for which i would have to drag down for.
In regards to the monthly data entry i was hoping for some way that only
the next row need contain the formula required. Could this be done throu
lists or tables?
I have just found some info which seems relevant
http://www.mvps.org/dmcritchie/excel/fillhand.htm
The "double-click fill handle" --> Excel fills the formula or sequence
down as far as the column to the left is filled with adjacent
data/formula" >>except that the double-click seems to have no result
on mac excel 2004?

 

[Bernard Rey]

I have a set of cells on the same row which all contain formulas.
Data is entered every month on a new row. Although i realise i could
fill the cells (containing the formula) downward to a point at which i
predict the data-entry to continue till - it seems messy. Also i have
100 sheets for which i would have to drag down for.
In regards to the monthly data entry i was hoping for some way that only
the next row need contain the formula required. Could this be done throu
lists or tables?

Yes I think the list manager should help you. It can even add a "Total" row
at the bottom if you're interested in it.

I have just found some info which seems relevant
http://www.mvps.org/dmcritchie/excel/fillhand.htm
The "double-click fill handle" --> Excel fills the formula or sequence
down as far as the column to the left is filled with adjacent
data/formula" >>except that the double-click seems to have no result
on mac excel 2004?

It works fine on Mac Excel 2004. There is no reason why it should not work.
Make sure you're the cells on the left are not empty and that you don't move
the mouse while double-clicking.

 

[Southwick]

James,
The method I use for this scenario is the "Data Form" However I don't
know if it will work for you as it does depend on the number of columns on
your worksheets.(32 Max) However, if your are working with 32 or less
columns, and I am assuming all columns have a header, simply select a cell
anywhere in the "list" and then select "form" from the Data Menu. As you
create each new entry, the formulas will be entered at the same time.
Dave