Hi,

Hope you are well. I actually need your urgent help on the report that I am currently working on. This is the first time I am using any blog to ask for the help in solving my query. So fingers cross and hoping that I will get the solution of my query.

Before explaining the problem, I would like to say that my background is databases related. So i mainly use SQL, mySQL, SAS and I am not that much expert in Excel. I am OK in using very basic functions, vlookups and pivot tables in excel but never ever worked on VB, excel macros. So I need your expert advise on the following issue.

I am working on the report where I have to get rid of the manual work(i.e. currently people are copy and paste the values from diffrent sources and it takes 2-3 hours). So basically I want to automate the report (would be great if we avoid drags and drops too as there are 40 plus metrics for each department and i am giving example in the attached work of 4 metrics only) to save the time and avoid manual work as mush as possible and along with this have to maintain the history of the previous days in history tabs too.

I am sending you the example in the excel file. The values that you are seeing in the report, for this I used vlookups which are working OK. And A,B,C,D are the departments. This report is the daily report but our offices are completely closed over the weekend so we don't get any weekend's data. The date that you are seeing on the report is the yesterday's date (For an instance today its 7th october but we will create the report for 6th october - previous day as everyday the data gets updated on server at 3am ). And on every monday, we create the report for Friday (i.e. last working day). So I want to do something so that every morning when I open the report then I would have been able to see the latest data on "P day" tab and its history gets maintained also i.e. the data for each department (A, B, C, D) on Previous day report also gets shifted/paste automatically to the relevant department history tabs on the same excel worksheet.

I want a logic something like wherever it sees that the "date" and "metrics name" of the previous day report (tab) matches with the "date" and "metrics name" in the A-history, B-History, C-History tabs for a specific department then the data gets updated. Along with this save all the previous day's data too in history tabs to maintain the history.

I have no idea at all whether we should use macro or excel functions to use this logic. Or if you have better logic than this. In future it might happen that we add few more metrics in the history reports of local departments only but they will not be shown on summary/previous day report. Because it might happen that the metrics that are important to dept A might not be important to other departments then there is no point to show them on summary sheet. Thats why I was thinking to use the logic where it matches the date and metrics name of specific department then only data gets updated in the respective history file.

The deadline to show the working solution to the report is 1 week only so I seriously seriously need URGENT HELP from you Experts.

Many thanks,
GK