Iam a bit in a problem in creating something from excel for last 1 week or so. If possible kindly help me on this one.

1) Firstly i like to make such a database using excel which can tackle the production and marketing details, make daily reports and on the basis of daily reports make weekly.

2)To tackle the daily reports of both production and marketing details i have made all the entries and formula on a single sheet. So that the entries of sale , expenses etc can be placed and calculated on a single sheet. This sheet is for daily reports.

3) Now as on the start of a new day we got to have a new sheet on which we can make new entries but that sheet has to be the same as that previous one having same formules in it but space left for new entries.
So the sheet has to be saved with some new name lets say the new name is the current date on which the sheet is being used.
So on each day we have a new sheet with a new name.

4) Now here is my problem. I want to make a weekly report but for this we got to have link with the daily reports and to maintain this link the source should be the same file as in the weekly report there has to be information at what date , day , time the expenses or sale has been made. Now this cannot be possible if the link of source sheet is changing , we have to do this mannually to change the link again and again.

5) Now i need to have a vba code for this such that weekly report is always has link with the desired cells of the sheet.

6) Like suppose if i want to show how many expenses has been made by Mr.Joe in a weekly , the following information has to be in the weekly report.
Mr. Joe
S.NO. Date Time Details Expenses
1
2
3
4
5

Now all the above cells should be linked with daily sheet so that they can pick values for there but the sheet is changing name every day.

Is there any way possible that we can create such a link that as the name of the sheet changes the link of the weekly report also changes and get connected with the new sheet and place the specified values in the next cells .e.g.


Mr. Joe
S.NO. Date Time Details Expenses
1 3-March 3:30A.M. Soap 300
2
3
4
5

Now on the next day as the sheet is changed so the above link of weekly report is changed in such a way that weekly report picks the values for Mr.Joe expenses from the same cell but from the new sheet and place them on the new row e.g.

Mr. Joe
S.NO. Date Time Details Expenses
1 3-March 3:30A.M. Soap 300
2 4_March 2:30A.M. Pen 150
3
4
5

and so on. Like i have done every thing to make it happen but no use. Kindly guide me how i can do this but please tell me in such a way that i can do modification as i need.