Excel report for old school excel users

I have an excel report situation which I wonder if there is an excel solution to it. It is mainly due to the user of this report. The users are really really old school who have very limited knowledge of excel other than simple input and simple formula. These users are also in the top management.

There is this "important" monthly report. The figures on this report is sourced from several other excel reports that are linked to database system.

The users of this monthly report are not excel frequent users. They are used to report that shows static input figures. Our old reporting system produced reports like that but the new report system is unable generate the monthly report this way.

In a way, it is ridiculous because it means that the preparer needs to copy data from other source reports and paste as "value" on this monthly report.

You can imagine if there is a change in the data system, the preparer needs to spend a lot of time to update the report manually.

We tried linking the data directly to the source reports but when users open the report, there will be system messages like "update links or not" etc. These system messages scared the hell out of them. They really hates it and will start questioning the report.

Is there a way to create this monthly report such that it looks like static report but it is linked to source reports........so that the preparer can just update the source reports and the data in the monthly report will be updated automatically?

Thanks! I will try it!

Hi, I have not tried it yet. Was tied up busy fire-fighting the confusion of excel updates of links for the users in my latest post.

but I could see that your script might help with this latest issue i have with excel too because some of the links are related to this "system connected" report.

Hi, I finally have time to try to work with the script you have written. Apparently the report I am trying to work on has "system formula" in each cell.
Example of the formula:

='Rolling Prognosis Cons 24 months - FC08.xlsm'!HsGetValue($C$1,""&$B$6&"#"&P$6&"",""&$A$23&"#"&$A183&"",
""&$B$4&"#"&P$4&"",""&$B$5&"#"&P$5&"",""&$B$14&"#"&P$14&"",""&$B$15&"#"&P$15&"",""&$B$7&"#"&P$7&"",
""&$B$23&"#"&$B183&"",""&$B$10&"#"&P$10&"",""&$B$12&

I tried using your script to do a test run without changing anything and it creates a report that is still linked to the system because of the cell formula.

I am at wit ends.....It seems to imply, I need to copy everything as "values" in another workbook and then manually update all the excel sum formula.

here is a sample view of the file that the script copied. I remove a lot of the information due to confidentiality.