PDA

View Full Version : auto update



rajagopal
05-24-2007, 01:45 AM
The details i enter in particular range of a master excel file should be updated automatically in another excel file.
This file should be saved as different file and the master file should be blank.
Please find the attached file for details.

rajagopal
05-24-2007, 02:37 AM
See my requirements given as comments in the attached file.

lucas
05-24-2007, 08:26 PM
I think this would be easier to do in Word. Set the template up and it never changes...each new file is cloned from the template and you can save it with the name you want.

Also your extensive use of merged cells would complicate any vba. Try to avoid using merged cells. Use center across selection instead.

rajagopal
05-24-2007, 08:52 PM
The data in cell range C60:L69 has to be updated in another file automatically. Here, there's no merged cells.

I want to reduce the manual effort that's why i need these functions to be executed automatically.
I had the tempalte in word earlier but faced difficulty in formatting. I prefer excel.
Can this be done in excel itself if not i provide a template in Word.

zv735
05-25-2007, 01:19 AM
1. you must key "Project ID" and else...
2. you close file with out save it will automatic save file in
"MOM_(Project ID).xls
3. file MOM.xls is Emty as Original File

Thank you

See Att File.

rajagopal
05-25-2007, 01:31 AM
This is fine for Saving the file.
My primary requirement is data updated in Description field (D60:D69), Responsibility field (J60:J69), Target date field (L60:L69) in MOM.xls has to be udpated automatically in a excel file (say the file name as Action.xls)
Each time, the user will use the MOM.xls template and the above code will save the file when user updates data in cell D8.
The action items entered in the above range should be saved in Action.xls.
Likewise, each and every time, the items entered in the above range should be saved next below to the previous update in Action.xls

Please help

zv735
05-25-2007, 02:39 AM
it will save every time when u change at inputrange in file action_date_Time.xls

see att file

rajagopal
05-27-2007, 09:16 PM
The target cell is not D8. It is D60:D69.
When the user updates the field D60:D69, the updates have to be updated in Action item tracker.xls
For ex. when user updates field D60:68, the same have to be updated in Action item tracker.xls in the range I7:I16 and a notification to the user that Action item tracker.xls is updated.
Next time, when the user updates new MOM template for another minutes of meeting in the cell range D60:64, the same has to be updated in Action item tracker.xls in the range I17:I21 i.e. start from the next row of previous update's final row.

Hope, i'm not confusing you.
See the attached Action item tracker.xls for reference

rajagopal
05-28-2007, 02:11 AM
I don't need the file to be saved for each and every cell update.
Instead, it should save only when the cell D69 is updated.

zv735
05-30-2007, 12:44 AM
please,see att file

rajagopal
05-30-2007, 01:25 AM
The values i update in MOM.xls is populated on Action item tracker.xls in the same row i.e. overwrite the previous update in the same row.
It should update it in next rows.
See the attached file with my requriements (given as comment).

Please help asap.

rajagopal
05-30-2007, 01:26 AM
Please find MOM.xls with my requirements

zv735
05-30-2007, 03:55 AM
see att file

rajagopal
05-30-2007, 05:24 AM
Two issues.

1. Data in J column in MOM.xls not updated in Action
item tracker.xls (It has to be updated in O column of Action item.xls)
2. Data in L column in MOM.xls is updated in O column of Action item.xls (It has to be updated in R column of Action item.xls)

Please help.

See the attache files for ref.

zv735
05-30-2007, 06:18 AM
I adjust some column in Action item tracker.xls
for make different column of Mom.xls and Action item tracker.xls are five

see att file

rajagopal
06-04-2007, 12:13 AM
Thanks.
is it possible to have two worksheet change events in the same sheet?
One for action item update from MOM.xls and another worksheet change event function for auto population of date.

Please guide.

rajagopal
06-04-2007, 01:57 AM
'This code for auto population of action item tracker

Dim LastLine As Long
Private Sub Worksheet_Change(ByVal Target As Range)
If TypeName(Intersect(Target, [InputRange])) = "Range" Then
Workbooks.Open Filename:=ActiveWorkbook.Path & "\Action item tracker.xls"
Windows("Action item tracker.xls").Activate
Sheets("Action item tracker").Select
If (Target.Column = 4) Then
Application.Goto Reference:=Workbooks("Action item tracker.xls").Worksheets("Action item tracker").Range("I65536")
LastLine = ActiveCell.End(xlUp).Row + 1
End If
Application.Goto Reference:=Workbooks("Action item tracker.xls").Worksheets("Action item tracker").Cells(LastLine, Target.Column + 5)
ActiveCell.Value = Target.Value
Windows("Action item tracker.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close
End If
Application.ScreenUpdating = True
End Sub

'This code for auto save when cell d8 is changed.
If TypeName(Application.Intersect(Target, Range("d8"))) = "range" Then
Application.Dialogs(xlDialogSaveAs).Show
End If

How can these two be merged as a single change event?

Please help asap....

rajagopal
06-04-2007, 05:11 AM
Also, column B (S.NO) has to be updated whenever the row is updated with the action items.
Give the code for the same.

Please help

rajagopal
06-04-2007, 05:24 AM
sorry for my repeated updates.
Cell D9, D13, I13 values in MOM.xls should be auto populated in action item tracker.xls in cell range E7, C7,G7 resply.
This will be the same for all action items of same MOM.

Please help.

zv735
06-04-2007, 10:49 AM
see att file

Thank you

rajagopal
06-04-2007, 10:51 PM
This works fine. Thanks a lot.
The user will update D60(Action item), J60(Responsibility), L60(Target date) in MOM.xls. when user completes to udpate L60, a message should appear that "Action items are successfully updated in Action item tracker".
When user don't update cell L60 but updates D60 & J60, an alert message should appear "Target date is mandatory"

When user deletes contents in any cell D60,J60, L60, a popup message "This Action item details are deleted from Action item tracker" should appear. The same should be for cell range D60:D69
Please help.

Is it possible to send auto email to the persons mentioned in Responsibility field "D60". The user will udpate this field with the names as available in Outlook address list.
The email has to be sent when system date = date mentioned in L60.
Msg subject:Action item reminder
Msg content (message as appear in cell D60 in MOM.xls)
The same is applicable for cell range D60:d69
If the system do not get the matching recipient name, an alert message has to appear when the file is opened.

Can this be done only when the file is opened or even if closed?

Please help in this complicated item.......

With Regards

rajagopal
06-06-2007, 10:59 PM
Can you help me with the above changes?

Please..

rajagopal
06-07-2007, 10:48 PM
Hi,
Can you help me with the above requirements?
Please....

rajagopal
06-08-2007, 08:18 AM
Hello are you there!!

rajagopal
06-10-2007, 11:45 PM
Can anybody help with my request????

lucas
06-11-2007, 07:50 AM
Is it possible to send auto email to the persons mentioned in Responsibility field "D60". The user will udpate this field with the names as available in Outlook address list.
The email has to be sent when system date = date mentioned in L60.
Msg subject:Action item reminder
Msg content (message as appear in cell D60 in MOM.xls)
The same is applicable for cell range D60:d69
If the system do not get the matching recipient name, an alert message has to appear when the file is opened.

Can this be done only when the file is opened or even if closed?

Please help in this complicated item.......

rajagopal,
You are right, this is a complicated item and from reading back through the thread...you have created a very complex file that I'm not sure you can maintain...someone else did all the work for you and your still asking for more.

I would suggest that you read and search the kb and the forum for email and see what you can come up with that will get you started in the right direction and then post back here with specific questions.

rajagopal
06-11-2007, 08:54 PM
I had read the helpdesk but unable to figure out the type of codes relevant to mine...
Can you give an idea about the codes / logic to be used.

Regards

zv735
06-11-2007, 10:05 PM
your problem very very complicated

What is your relevancy?

Please send me in bullet point

1. I want...
2. I want...
3. I want...

something like that

Thank you

rajagopal
06-12-2007, 01:41 AM
1. Date of meeting, Type of meeting, Review period has to be udpated for every action item in action item tracker.xls
2. To display Calendar in cell range L60:L69 when user select the cell to input target date in MOM.xls ( I tried it with user form but got error)

I need these 2 requests only.
Thanks for all the help you've provided.
See attached file for ref.

zv735
06-12-2007, 07:36 AM
Something Like That

See Att. File

Thank You

rajagopal
06-12-2007, 09:07 PM
1. Date of meeting, Type of meeting, Review period has to be udpated for every action item in action item tracker.xls ------ This is being updated with #REF...
Please find attached Action item tracker.xls for your ref.

Please help...

rajagopal
06-12-2007, 09:17 PM
Sorry.....
In your code, you had the file name MOM12.xls but i have MOM.xls
Sorry to bother you.
Thanks a lot for all your help.
By the by, May i know where you are and your occupation!! if you wish to disclose..

Regards

zv735
06-13-2007, 01:39 AM
As Sure this red font is Correct File

ActiveCell.Value = ['[MOM.xls]MOM'!$D$13]

rajagopal
06-13-2007, 02:04 AM
May i know your name, location.. yours coding skills are excellent.

I've a request below.

Is it possible to send auto email to the persons mentioned in Responsibility field "D60". The user will udpate this field with the names as available in Outlook address list.
The email has to be sent when system date = date mentioned in L60.
Msg subject:Action item reminder
Msg content (message as appear in cell D60 in MOM.xls)
The same is applicable for cell range D60:d69

Regards

zv735
06-13-2007, 03:37 AM
I don't know about send mail via Excel
but Excel possible to send auto email
so, i'm sure that u can find ur Solution by old Threads in Forum


My Location : Thailand
Thank you :beerchug:

rajagopal
06-14-2007, 02:19 AM
Codes available in old threads are not working for me.
I've pretty good theoretical knowledge, begins to learn macros.
Can you provide the codes to generate auto emails?
You can refer the previous threads in this post for my exact requirement.

Thailand!! Beautiful country.. What are you doing?? I couldn't get your good name as yet.

Regards

rajagopal
07-06-2007, 04:30 AM
I've added another field in MOM & action item tracker. It gets updated correctly.
When i delete the content while entering, all the content gets deleted from action item tracker except "Status" field.
Please help...
Refer the attached MOM & action item tracker for ref.

Regards

zv735
07-06-2007, 04:55 AM
see att. file

rajagopal
07-06-2007, 05:43 AM
No updates happening.
The action item tracker has not been updated even for a single field which it worked well earlier.
please clarify

Regards

rajagopal
07-06-2007, 07:59 AM
Sorry. it gets updated in all the fields.
But my problem has not resolved.
when i delete values in MOM.xls, everything gets deleted in Action item tracker except "Status" field.

Please help.

rajagopal
07-09-2007, 12:00 AM
Sorry i was totally confused.
No updation take place in your test01.zip
Please refer my earlier attached file test.zip
I need only one requirement. Consider the below scenario.
You've opened MOM.xls and entering 4 action items - status field will be updated as "Open" in action item tracker.xls.
When you entered all, you want to delete the 4th action item.
Everything gets deleted except status field in action item tracker.
This has to be deleted.
I tried all the options but unable to fix it.


Please help.

rajagopal
07-10-2007, 12:44 AM
Can you help me out?

rajagopal
07-10-2007, 05:36 AM
Please help me out..