Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with setting up a complex directive
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with setting up a complex directive
*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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EU Working Time Directive | Excel Discussion (Misc queries) | |||
Complex formula help needed | Excel Worksheet Functions | |||
Complex forumula help needed | Excel Worksheet Functions | |||
setting complex print area | Excel Programming | |||
setting complex print area | Excel Programming |