Excel - Sending Spreadsheet Data

[Robyne]

We have a balancing spreadsheet for each branch, with the calculated cells protected. The next day, another user goes to each branch server, finds the previous days' balance sheet, prints it, then keys in selected totals into a master balancing sheet. From my limited knowledge of Excel, my options are to create a master workbook containing a worksheet for each branch on the main server, and have those totals flow to the master sheet automatically. In this scenerio, I may run unto network traffic issues. I don't know, I haven't tested it yet. Or, create a unprotected worksheet and send the XML data to be imported into a master workbook, again with the totals flowing to the master. I don't want to take the chance of the users overwriting the calculations, and it appears I can't send XML data from a protected worksheet. Anyone have any other ideas? Thanks!

 

[SkipVought]

H,

I guess that this branch data results in daily activity; cash in/cash out, yes?

If this data is on the server, in a known folder with a known workbook name, there is really no need for anyone to manually open each one and retrieve the data. Record a macro to do this. BTW, regarding the last statement, you 1) make a list of the sources and 2) record ONE complete retrieval and then modily the code to loop thru the list to get tem all.

Second, it would be a mistake to put each branches/day's worth on separate sheets. Separate sheets makes reporting and data analysis extremely difficult. Excel has so many tools that can be used to report and analyze a table. It is a typical novice spreadsheet mistake to chop up data by branch, days, manager -- whatever. BIG MISTAKE!

Skip,

_[red][/red]

 

[Robyne]

Skip,

This wasn't my project to begin with so I can only assume the reason each branch has it's own sheet is because they're held on their local server. I thought of a macro, but I don't have a lot of experience with them and I wasn't sure how to use a variable in one, as they have a template and do a Save As using some sort of combination of the branch name or number and the date. Too, I don't believe reporting and analysis even enters the equation, at the end of the day, they just want to balance!

Would you suggest a master workbook with a sheet for each branch each day or a master workbook for each branch to hold a month? Or what would you suggest? Thanks!

 

[SkipVought]

One workbook. One worksheet for EVERYTHING.

"I don't believe reporting and analysis even enters the equation, at the end of the day, they just want to balance!"

That's a REPORT.

"Save As using some sort of combination of the branch name or number and the date."

Not a problem as long as it follows a consistent format.

Unless you want to do ALOT of manual work EVERY DAY, a macro is the ONLY way to go.




Skip,

_[red][/red]

 

[Robyne]

Let's say I have one workbook. If I share it, I can't protect it. If I don't protect it, I can't keep the users from overwriting the calculated cells. And you know they will accidently do it.

 

[SkipVought]

You are going to have to get involved in coding a solution. Your're a bit beyond native Excel functionality if you need to protect AND do other things. When you go to do something that can't be done with a protected workbook/worksheet, you have to UNPROTECT, do the thing and then PROTECT.

Skip,

_[red][/red]

 

[Robyne]

And that leads me back to my first idea oh, so many months ago, which was using macros to bring the totals from the single sheets into the Master sheet. I guess I'm going to learn macros. Either that or move the whole shebang to Access. Then I'll learn that! Thanks for your steering and ideas!