Linking spreadsheets to each other

**jfixler** · 01-15-2011, 01:19 AM

Hello,
I volunteer for a small non profit and we have a shop that we run where we sell about 100 products. The shop is staffed by volunteers. Here's what we are trying to do:

We want to keep one spreadsheet that has an inventory of our products so that we can see when we are low on items. We like for this sheet to automatically update from our sales worksheets. Each day, when a volunteer opens the shop, they open up an excel template that has a list of the products and their costs, where they record their daily sales by marking the number of each item that they sell (in the row for that item). At the top the spreadsheet has some tallies so they can see how much they sold for the day. Next to each item their is a column for cash, a column for credit, and a column for gift certificates. At the end of the day, they save the spreadsheet as a new file in a designated folder with the file name MM.DD.YY.DailySales.

What I am trying to work out is if there is a way to take the total numbers sold column from all the spreadsheets in the daily sales folder and automatically add a column for each one in the inventory spreadsheets (preferably with the date at the top) so that the inventory spreadsheet can stay up to date without anyone having to copy and paste the sales totals column into the inventory spreadsheet). Does anybody know if this is possible and how I might go about it.

It seems like it wouldn't be that hard if there was just one sales spreadsheet, but because we are creating a new one each day, it gets a little trickier. The staff wants us to continue making new ones each day since we are volunteers and then there is less risk of us screwing things up than if we were all working from the same spreadsheet.

Thanks!

**6StringJazzer** · 01-15-2011, 06:02 AM

Originally Posted by jfixler

Does anybody know if this is possible...

In Excel anything is possible

There are three approaches I can think of to this type of problem.

1. You said that you want a column added automatically in the master inventory workbook for each day that there exists a daily workbook. If you are willing to sacrifice this automation and put those columns in advance, you can solve this with Excel formulas. There are two ways to do this.

1a. Set up the columns for each day in advance. Then use formulas that refer to the corresponding daily file such as

=SUM([01.15.11.DailySales]Sheet1!$A$6:$K$6)

I have no idea what your actual calculation needs to be but you get the idea.

Pro: Once you set it up no additional work is needed.
Con: Set up is a bit of a pain. Extra work to set up a new day.

1b. Set the columns for each day in advance. Put the date in row 1. Then use formulas that refer to the corresponding daily file such as:

=SUM(INDIRECT("["&TEXT(B$1,"dd.mm.yy")&".DailySales]Sheet1!$A$"&ROW()&":$K$"&ROW()))

Pro: Once you set it up no additional work is needed. Easier to add new days just by copying a column.
Con: Formula is a bit more complex. And the worst part is that all the daily files have to be open for INDIRECT to work. This often renders this type of solution unacceptable.

2. Write code in VBA to do this. I can't offer any actual code without seeing both your daily file and your master inventory file, but it's not a hugely complex problem. The user will have to allow macros to run, so if you have multiple users looking at the master inventory you will have to train them to do that correctly. But otherwise it would be 100% automated.

Pro: Opportunity to learn VBA
Con: Have to learn VBA
_______________________________________________
By the way, I would suggest that you name your files

YYYY.MM.DD.DailySales

That way if you sort by file name you also get a sort by date.

**jfixler** · 01-22-2011, 01:36 AM

Originally Posted by 6StringJazzer

In Excel anything is possible

Thanks 6StringJazzer! This is really helpful.

It sounds like the macros route is going to be more foolproof (and since we're dealing with a bunch of volunteers that's a selling point). So I guess the next question is, where does a tech-savy-ish guy who does not know much about macros go to teach himself how to do something like this?

**6StringJazzer** · 01-23-2011, 10:11 AM

I first learned VBA for Excel from a book by John Walkenbach, an accomplished author in this area. There are other good sources as well. www.ozgrid.com is a good online resource (they also have a message board).

If you hit ALT-F11 you will bring up the VBA development environment. Clicking on the question mark icon on the toolbar will bring up Help, which includes a Language Reference. It's not great as a tutorial for beginners, more suitable as a reference guide, but for a tech-savvy-ish guy it might be enough. I have a background in software development. Learning the VBA language is very easy, but learning the Excel object model and how to do things in the most effective way is a longer process. For example, a programmer using conventional techniques might try to locate a value in a worksheet by iterating through each cell, overlooking the fact that the Excel object model includes a Find method on Range that will do it much faster and with less code.

If you attach an example of a daily file plus the master inventory file I might be able to offer more specific advice.