Prevent functions from changing when new rows are added

[Gregg]

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.

 

[Gregg]

Teylyn, thanks for your help. Based on your input, I read about Data Tables
but I'm not sure how this will help me or how to configure it. First, let's
make sure we're on the same page. This 'timesheet' worksheet will start off
containing 2 rows for each date, and then I can insert more rows for any
given date if more tasks are performed (or I can delete a row if fewer tasks
are performed). Knowing this, if I configured the worksheet as a Data
Table, what cell would I enter as 'Row Input Cell' or 'Column Input Cell'?

Thanks,

Gregg.

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

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/showthread.php?t=158296

Microsoft Office Help