Prevent functions from changing when new rows are added
 

I have a worksheet configured as a timesheet, where each row represents a
task performed. For any given day, I track the task(s) performed and the
hours spent on each task, and there are many functions in the worksheet.
The worksheet contains the following columns:
Column A: blank column (not used for anything).
Column B: series of dates (with a blank row in between each date).
Column C: tasks performed, manually entered.
Column D: hours worked on the task.
Column E: hours worked for the week. There's a function in each cell where,
if it's Sunday, a sum of the week's work will be displayed.
Column F: hours worked for the month. There's a function in each cell
where, if it's the last day of the month, a sum of the month's work will be
displayed.

Here's my problem: Whenever I add an extra row (in order to display
additional tasks performed that day), the function in column E changes. It
starts off as:

=IF($B18="","",(IF(WEEKDAY($B18)=1,(SUM($D$9:$D18)-SUM($E$9:$E17)),"")))

.... but when I add a new row, the function in that new row ends up as:

=IF(WEEKDAY($B19)=2,SUM($D$9:$D18)-SUM($E$9:$E17),"")

How can I ensure that the functions in the new row follow the same
conventions as in the row above? Thanks very much.

Gregg.

Prevent functions from changing when new rows are added
 

Set up your data entry table as a list (XL03) or a table (XL07) and new
rows will receive formulae and formats from the row above.

hth

teylyn

Gregg;572941 Wrote:
I have a worksheet configured as a timesheet, where each row represents
a
task performed. For any given day, I track the task(s) performed and
the
hours spent on each task, and there are many functions in the
worksheet.
The worksheet contains the following columns:
Column A: blank column (not used for anything).
Column B: series of dates (with a blank row in between each date).
Column C: tasks performed, manually entered.
Column D: hours worked on the task.
Column E: hours worked for the week. There's a function in each cell
where,
if it's Sunday, a sum of the week's work will be displayed.
Column F: hours worked for the month. There's a function in each cell
where, if it's the last day of the month, a sum of the month's work
will be
displayed.

Here's my problem: Whenever I add an extra row (in order to display
additional tasks performed that day), the function in column E changes.
It
starts off as:

=IF($B18="","",(IF(WEEKDAY($B18)=1,(SUM($D$9:$D18)-SUM($E$9:$E17)),"")))

.... but when I add a new row, the function in that new row ends up
as:

=IF(WEEKDAY($B19)=2,SUM($D$9:$D18)-SUM($E$9:$E17),"")

How can I ensure that the functions in the new row follow the same
conventions as in the row above? Thanks very much.

Gregg.


--
teylyn

Telyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: http://www.thecodecage.com/forumz/member.php?userid=983
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158296

Microsoft Office Help