Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changes in my setup?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changes in my setup?
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 On Nov 11, 1:13 am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changes in my setup?
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 "Pete_UK" wrote: 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 On Nov 11, 1:13 am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changes in my setup?
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 On Nov 11, 3:06 am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote: 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 "Pete_UK" wrote: 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 On Nov 11, 1:13 am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote: 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page Setup | Excel Discussion (Misc queries) | |||
Setup | Excel Discussion (Misc queries) | |||
View Setup | Setting up and Configuration of Excel | |||
setup | Excel Worksheet Functions | |||
currency setup | Excel Discussion (Misc queries) |