Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering values?
Hi,
I am working a spreadsheet that contains banking information. There are several sheets, one is recurring bills and another is debits. What I would like to do is have the debit sheet reflect what recurring bill has been paid on each date. For example, bill ABC has been noted as paid on Monday 4/5 at a value of $20.00(by selecting "yes" in column B, column C's value is "Monday" (using the TEXT(WEEKDAY(a1),"dddd" function). In worksheet debit, I would like column Monday to reflect the amount of $20.00. In addition, all recurring bills and debits are seperated into weeks (wk 1, wk 2, etc), so not only do I need to reflect what has been paid on Monday, but also what has been paid on Monday in week one. Another note, how would I forward a value to the next cell, say if 2 payments were made on Monday, week one? Thank you in advance, Megan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering values?
Hi Megan,
I guess I am a little confused. What is the Dr? Reduced A/P and I assume the Cr is cash? You have a list of recurring invoices that are paid on the same day each month, I think and then some invoices that are not recurring, but are paid on specific dates? I this what is happening? Not sure, but it appears that a "yes" must be present for an invoice to be paid, if this is so, is it so for recurring invoices too? Then you have other sheets, which are each of of the week, ie Monday. Maybe a sheet for every day of the month or maybe this is 5 sheets, for each weekday ie Mon - Fri? You want totals on each of the 5 sheets of all payments made, divided by week. Is this what you are looking for? Thanks, David -----Original Message----- Hi, I am working a spreadsheet that contains banking information. There are several sheets, one is recurring bills and another is debits. What I would like to do is have the debit sheet reflect what recurring bill has been paid on each date. For example, bill ABC has been noted as paid on Monday 4/5 at a value of $20.00(by selecting "yes" in column B, column C's value is "Monday" (using the TEXT(WEEKDAY(a1),"dddd" function). In worksheet debit, I would like column Monday to reflect the amount of $20.00. In addition, all recurring bills and debits are seperated into weeks (wk 1, wk 2, etc), so not only do I need to reflect what has been paid on Monday, but also what has been paid on Monday in week one. Another note, how would I forward a value to the next cell, say if 2 payments were made on Monday, week one? Thank you in advance, Megan . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering values?
Hi David,
(At an effort to be clear, this response is very long. Thank you, thank you, thank you for taking the time to look at it!) I guess the situation can get confusing. What I have is a multiple sheet workbook that serves the purpose of tracking bills and banking. One sheet shows a summary of all activity (What credits and debits have been posted to the account). The next sheet, Credits, shows what has been deposited in the aco****. It is divided into 5 weeks and within each week is a Sun through Sat heading. Each day a deposit is made, the user would enter the amount into the correct cell (i.e. a $300 deposit on monday). The debit sheet works the same way. A user would have to enter every deduction in the correct cell (i.e. a $100 check on Monday, a $20 withdrawl on Tuesday, etc). The reccurring bills sheet is where a user can enter bills that occur each month on a consistent basis, like credit cards, phone bills, etc. On this sheet, the user enters info. on when it is due, amount and wether or not it has been paid (list value of yes,no). Depending on the date due, the sheet calculates how much in recurring bills is due each week, and puts that amount in the debit sheet for the cooresponding week. (so if $200 was the amount due in week 4, in the debit sheet for week 4 the $200 would be shown as the weekly total for recurring bills. Any additional deductions entered by the user would be added to this total for the total weeks debits.) There are two problems with this. First the user would have to enter each recurring bill twice; Once in the recurring bill page and a second time in the debit sheet when it was actually paid. Secondly, if a bill is due in week 3, it is probally going to be paid in week 2, or week 1. What I would like to happen is when a user selects, yes, the bill has been paid, for the date and day to be recongized and have the amount paid automatically entered into the debit sheet. I ran into a problem because I don't know how to have Excel see two dates as being equal, espicially since the debit sheet just has days, not dates. (the column heading are only Mon, Tue, etc., there are no 'real' dates as in 4/7/04.)So what I did was use the 'dddd' function to show what day of the week the bill was paid on, but all this gives me is what bills in week 1, week 2, etc were paid on Mondays,Tuesdays, etc. I need this to realize that a bill was paid on such and such a day in such and such a week and enter that amount in the debit sheet. Recurirng Bills Date Bill Amount Due Week Paid? Date Paid Day Paid Credit Card $20 16th 3 yes 4/6/04 Tuesday Debit Sheet Week 2 Mon Tue Wed Thur Fri .......... $20 See, the bill is due on week 3, but is paid in week 2. I do not want the user to have enter recurirng bills in the debit sheet. I just want them select, yes, it has been paid. Megan -----Original Message----- Hi Megan, I guess I am a little confused. What is the Dr? Reduced A/P and I assume the Cr is cash? You have a list of recurring invoices that are paid on the same day each month, I think and then some invoices that are not recurring, but are paid on specific dates? I this what is happening? Not sure, but it appears that a "yes" must be present for an invoice to be paid, if this is so, is it so for recurring invoices too? Then you have other sheets, which are each of of the week, ie Monday. Maybe a sheet for every day of the month or maybe this is 5 sheets, for each weekday ie Mon - Fri? You want totals on each of the 5 sheets of all payments made, divided by week. Is this what you are looking for? Thanks, David -----Original Message----- Hi, I am working a spreadsheet that contains banking information. There are several sheets, one is recurring bills and another is debits. What I would like to do is have the debit sheet reflect what recurring bill has been paid on each date. For example, bill ABC has been noted as paid on Monday 4/5 at a value of $20.00(by selecting "yes" in column B, column C's value is "Monday" (using the TEXT(WEEKDAY(a1),"dddd" function). In worksheet debit, I would like column Monday to reflect the amount of $20.00. In addition, all recurring bills and debits are seperated into weeks (wk 1, wk 2, etc), so not only do I need to reflect what has been paid on Monday, but also what has been paid on Monday in week one. Another note, how would I forward a value to the next cell, say if 2 payments were made on Monday, week one? Thank you in advance, Megan . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering values?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfering cell values progressively through worksheets | Excel Worksheet Functions | |||
Transfering Workbook | Excel Discussion (Misc queries) | |||
Transfering Values | Excel Worksheet Functions | |||
transfering forms | New Users to Excel | |||
Transfering Info | Excel Worksheet Functions |