Help needed with setting up a complex directive

[cxlough41]

i am running xp and office 2003 pro.

i need to take income information (wb1) such as;
1. date
2. "cash" or the check no.
3. amount
and send it to a different workbook (wb2).

i need to take any outstanding amount still owed (this can be found on same
page(wb1)) and place it in (wb2).

i need to take job no. (wb1, sheet2) and place in (wb2), this cell should
auto create a hyperlink back to the page where income report is in (wb1, for
viewing/reviewing).

(wb2) needs to autosave after transfer of cell contents.

All of this together needs to take place just b4 (wb1) save/close, or every
time there is a change in income report worksheet(wb1), whether or not (wb1)
has been saved with the info/change. NO REPEATS

following are the cells and sheet names along with needs;

(wb1)= CT-MCWB005a

customer info= sheet2

job#= I14:J14(merged)((this cell has a long equation which self generates
the job # based on other cell contents))**this info needs to goto (wb2),
(wb2)needs to check this info for a date(part of job#), then (wb2) needs to
place this info in the proper month in column "A"
jan= A16 - A57
feb= A67 - A108
mar= A118 - A159
apr= A169 - A210
follow this pattern for entire year till dec which is A577 - A617 and jan
next yr= A628 - A666

income report= sheet16

Date Ck Recvd= G16:G27 goto ((wb2),column b (all of these same row as job#))
Check#=H16:H27 goto ((wb2), col c
Amount= J16:J27 goto ((wb2), col e
outstanding= C15:D15(merged) goto ((wb2), col d
if multiple checks recvd on a single job number all must be listed

this is what it should look like:
(wb2)
A B C
D E
invoice # date recvd Check no. outstanding
amount recvd
BEMI010606 01/16 322 10.00
1623.65
01/20 345 0.00
10.00

in this example in the invoice # BEMI is useless but 010606 is the date
customer called, this is also the workbook file name when saved. notice the
10.00 outstanding... this should update to 0.00 in all relative cells to this
invoice, if paid in full. outstanding should only have a value if still owed
to me.

(wb2)= CT-IR-MCWB

i will take care of entering the full path for saving if you can note where
to add.

i think this will do it.

incase it is needed - the formula for the job number is:
=(UPPER(LEFT(C12,2))&UPPER(LEFT(C10,2))&TEXT(MONTH(I1),"00")&TEXT(DAY(I1),"00")&RIGHT(YEAR(I1),2)&"-"&TEXT(HOUR(H1),"00")&TEXT(MINUTE(H1),"00"))

Hope some one can help this is just crazy to me. Thanks in advance for any
help.

[cxlough41]

*NOTE - if value of cell containing chk # in (wb1) is blank or "cash" (wb2)
should return a value equal to "cash"

"cxlough41" wrote:

> i am running xp and office 2003 pro.
>
> i need to take income information (wb1) such as;
> 1. date
> 2. "cash" or the check no.
> 3. amount
> and send it to a different workbook (wb2).
>
> i need to take any outstanding amount still owed (this can be found on same
> page(wb1)) and place it in (wb2).
>
> i need to take job no. (wb1, sheet2) and place in (wb2), this cell should
> auto create a hyperlink back to the page where income report is in (wb1, for
> viewing/reviewing).
>
> (wb2) needs to autosave after transfer of cell contents.
>
> All of this together needs to take place just b4 (wb1) save/close, or every
> time there is a change in income report worksheet(wb1), whether or not (wb1)
> has been saved with the info/change. NO REPEATS
>
> following are the cells and sheet names along with needs;
>
> (wb1)= CT-MCWB005a
>
> customer info= sheet2
>
> job#= I14:J14(merged)((this cell has a long equation which self generates
> the job # based on other cell contents))**this info needs to goto (wb2),
> (wb2)needs to check this info for a date(part of job#), then (wb2) needs to
> place this info in the proper month in column "A"
> jan= A16 - A57
> feb= A67 - A108
> mar= A118 - A159
> apr= A169 - A210
> follow this pattern for entire year till dec which is A577 - A617 and jan
> next yr= A628 - A666
>
> income report= sheet16
>
> Date Ck Recvd= G16:G27 goto ((wb2),column b (all of these same row as job#))
> Check#=H16:H27 goto ((wb2), col c
> Amount= J16:J27 goto ((wb2), col e
> outstanding= C15:D15(merged) goto ((wb2), col d
> if multiple checks recvd on a single job number all must be listed
>
> this is what it should look like:
> (wb2)
> A B C
> D E
> invoice # date recvd Check no. outstanding
> amount recvd
> BEMI010606 01/16 322 10.00
> 1623.65
> 01/20 345 0.00
> 10.00
>
> in this example in the invoice # BEMI is useless but 010606 is the date
> customer called, this is also the workbook file name when saved. notice the
> 10.00 outstanding... this should update to 0.00 in all relative cells to this
> invoice, if paid in full. outstanding should only have a value if still owed
> to me.
>
> (wb2)= CT-IR-MCWB
>
> i will take care of entering the full path for saving if you can note where
> to add.
>
> i think this will do it.
>
> incase it is needed - the formula for the job number is:
> =(UPPER(LEFT(C12,2))&UPPER(LEFT(C10,2))&TEXT(MONTH(I1),"00")&TEXT(DAY(I1),"00")&RIGHT(YEAR(I1),2)&"-"&TEXT(HOUR(H1),"00")&TEXT(MINUTE(H1),"00"))
>
> Hope some one can help this is just crazy to me. Thanks in advance for any
> help.