Linking Master yearly sheet to individual monthly sheets

[rks]

Hi ,


I am working on a enquiry tracking sheet which has tabs Jan to Dec, Yearly enquiry list, Q1-Q4 graphs and Yearly graph . I have included a sample copy of the original sheet( which has more columns and data, but is confidential).


A sample has been uploaded at the below link for more clarity


https://www.dropbox.com/s/x8spcw15uq08vkl/sample_Enquiry_tracker.xls


Could anyone kindly help me to figure out the following requirements:


1. There needs to be a Control sheet( a sample is included) - Any changes or updates in this sheet( may be addition or deletion of names or regions) should reflect everywhere in all tabs ( monthly yearly and graph table entries). For Eg: Under enquiries assigned to subteams, if Ramesh is deleted, or say Ramesh is changed to Mahesh, or say a new name is added, it should reflect everywhere- in columns and lists of each tab and the table entries of graphs.


2. Yearly sheet (master sheet)is the main sheet and is filled with data as and when enquiries come in. The data from here has to be transferred (replicated) in individual months Tabs,-Jan, Feb etc automatically and be seen as shown in sample.


3. Graphs need to be also derived from the master sheet itself if possible


Kindly help.

 

[jason]

if you are entering / storing all if the monthly data on the yearly sheet, IIWM, i woul make the monthly tables PivotTables from the yearly sheet. that way each monthly sheet would be a pivot table filtered for that specific month. if any data in the yearly sheet changes, the pivottables (once refreshed) will updated as well.

 

[rks]

Could you kindly let me know how can it be done for data on this sheet, as am new to excel and am unable to figure out.

 

[rks]

Hi,


Also i would need the monthly sheets to look replica of yearly sheet except that it should contain data from Jan 1st to Jan 31. Similarly Feb sheet should contain data from Feb 1st to Feb 29 and so on. Pivot table would truncate the field names.