Transfering values?

[Megan]

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

 

[David]

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

 

[Megan]

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 acocunt. 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

 

[David]

Hi Megan,

Sorry it tok so long to get back and take a look at this,
but it has been a busy week. Not even sure if you are
going to get to this and see it. I am sorry, but I am
still confused. If it does not bother you and there is
not any ID info on the sheets, ie it is not confindential
or what is confindential can be removed. I will look at
the file, if you want to send it. Ususally don't put my e
mail in these things and you can see I did not put it
above. End up getting too much much junk. But my e mail
is (e-mail address removed). Just from the way it sounds, I
think you will need to put a complete date in
the "headers", if you want the invoices paid to go to
the "right" week. Don't know how else you you be able to
ID where to put it without a complete date.

Thanks,
David