Changes in my setup?

[Mike Rogers]

I have five columns that contain the names of the people that pay me a
monthly fee. I have twelve rows, one for each month. I have it set up that
when I select the cell for a client for the current month a “check†mark is
placed. (This is done via a Worksheet_SelectionChange macro) I then use an
“If†statement [=IF(B7="P",$B$5,"")] that references a cell with what each
client pays each month to determine my total monies received for each month
based on those “Check†marks. All this works fine. The problem I have is
when I need to change the amount paid each month by changing the amounts in
those abolute referenced cells it changes all the months already paid,
because of the Absolute References. Short of just entering the amounts
received each month how can I lock in those months already paid and change
that amount paid for the balance of the year? This may happen only once a
year, and it may happen once in a couple years. Any ideas on changing my
setup would also be appreciated if it will fix my problem.

xl 2000

Mike Rogers

 

[Pete_UK]

Assume your formulae are already copied down beyond this month. When
you want to increase your fees, insert a new row and put your fees in
that row for each client (you might want to give it a different
background colour for emphasis) - assume it is row 20. Then highlight
all the cells below that row and CTRL-H (Find & Replace):

Find what: $5
Replace with: $20

click replace all. Your formulae beyond the new fees will now pick
those up, rather than the fees in row 5, and your earlier fees will
not be affected.

I track clients' electricity bills this way, and the charges change
every year.

Hope this helps.

Pete

 

[Mike Rogers]

Pete_UK

Thanks, I think that will get me going. Now that I have a platform I will
try to record a macro while using your suggestion. There are will be two
things I need to figure out. 1) How to have the macro start at the active
row, & 2) How to change the named range that the other macro uses to place
the check marks.

Thanks for the help!!!

Mike Rogers

Assume your formulae are already copied down beyond this month. When
you want to increase your fees, insert a new row and put your fees in
that row for each client (you might want to give it a different
background colour for emphasis) - assume it is row 20. Then highlight
all the cells below that row and CTRL-H (Find & Replace):

Find what: $5
Replace with: $20

click replace all. Your formulae beyond the new fees will now pick
those up, rather than the fees in row 5, and your earlier fees will
not be affected.

I track clients' electricity bills this way, and the charges change
every year.

Hope this helps.

Pete

I have five columns that contain the names of the people that pay me a
monthly fee. I have twelve rows, one for each month. I have it set up that
when I select the cell for a client for the current month a "check" mark is
placed. (This is done via a Worksheet_SelectionChange macro) I then use an
"If" statement [=IF(B7="P",$B$5,"")] that references a cell with what each
client pays each month to determine my total monies received for each month
based on those "Check" marks. All this works fine. The problem I have is
when I need to change the amount paid each month by changing the amounts in
those abolute referenced cells it changes all the months already paid,
because of the Absolute References. Short of just entering the amounts
received each month how can I lock in those months already paid and change
that amount paid for the balance of the year? This may happen only once a
year, and it may happen once in a couple years. Any ideas on changing my
setup would also be appreciated if it will fix my problem.

xl 2000

Mike Rogers

 

[Pete_UK]

You're welcome, Mike - thanks for feeding back, and good luck with the
macro (I haven't bothered to do that, as it is so quick to do
manually).

Pete

Pete_UK

Thanks, I think that will get me going. Now that I have a platform I will
try to record a macro while using your suggestion. There are will be two
things I need to figure out. 1) How to have the macro start at the active
row, & 2) How to change the named range that the other macro uses to place
the check marks.

Thanks for the help!!!

Mike Rogers

Assume your formulae are already copied down beyond this month. When
you want to increase your fees, insert a new row and put your fees in
that row for each client (you might want to give it a different
background colour for emphasis) - assume it is row 20. Then highlight
all the cells below that row and CTRL-H (Find & Replace):

Find what: $5
Replace with: $20

click replace all. Your formulae beyond the new fees will now pick
those up, rather than the fees in row 5, and your earlier fees will
not be affected.

I track clients' electricity bills this way, and the charges change
every year.

Hope this helps.

Pete

I have five columns that contain the names of the people that pay me a
monthly fee. I have twelve rows, one for each month. I have it set up that
when I select the cell for a client for the current month a "check" mark is
placed. (This is done via a Worksheet_SelectionChange macro) I then use an
"If" statement [=IF(B7="P",$B$5,"")] that references a cell with what each
client pays each month to determine my total monies received for each month
based on those "Check" marks. All this works fine. The problem I have is
when I need to change the amount paid each month by changing the amounts in
those abolute referenced cells it changes all the months already paid,
because of the Absolute References. Short of just entering the amounts
received each month how can I lock in those months already paid and change
that amount paid for the balance of the year? This may happen only once a
year, and it may happen once in a couple years. Any ideas on changing my
setup would also be appreciated if it will fix my problem.
xl 2000
Mike Rogers- Hide quoted text -

- Show quoted text -