Excel Spreadsheet Updating
May 11, 2004 1:01 PM   Subscribe

Excel query. I have two Excel spreadsheets that I want to have automativally update each other. But, the format that they are in is generally incompatible. These are used as scheduling documents for the place where I work.

One spreadsheet is very wide. It lists each individual person at the top and their personal schedule below it. It lists breaks using "ONE" as the first half of a 30 minute block, "TWO" as the second half of a 30 minute block, and "LONG" as the entire 30 minute block.

The other spreadsheet is much less wide. It has 7 days listed and three columns. One is for "LONG" one is for "ONE" and the third is for "TWO." In each column, each person's name is entered in the appropriate block as defined in the first spreadsheet.

Can I get those two to talk to each other? Can I tell the first spreadsheet that if the word "TWO" is in a certain field to put the name of that person in another field on another spreadsheet?
posted by ajpresto to Computers & Internet (1 answer total)
Yeah, you could do that. But that way lies madness:

-which spreadsheet holds the correct information? How do you resolve conflicts when you synchronize?
-alright, let's validate entries so that they can't be in conflict. Can you even run an excel macro by force at the time a cell is updated? I'm an Access guy, I dunno. What are you going to do about people turning off macros (it's that way by default and for good reason)
-how wide is this spreadsheet getting? It's getting to be a pain in the ass to work with, isn't it?

Anyway, if I had to do this in Excel, I would establish one and only one worksheet to enter data. Then, I'd look up how to embed buttons in a worksheet, get the O'Reilly book on Excel Macros, and churn out some VBA that would write current data to whatever other formatted (read-only) templates I needed when the button is pressed. If you are familiar with Office programming and the way MS does the object models, it's not very hard to learn how to just write to an Excel template in code. There is a lot of good tutorial material on this kind of Excel trickery out there, too.
posted by crunchburger at 4:04 PM on May 11, 2004

« Older Indexing CDs   |   How can I solve my annual ant problem? Newer »
This thread is closed to new comments.