Automatically update

[hunter122976]

Hope I can explain this, so it makes sense...

I have around 20 customers that I have to keep prices on. Each customer has it's on folder with workbooks I've named for each style they have. Some of the customers have close to a hundred styles 'workbooks'. The workbooks each have formulas in them, to allow me to decide what price to use for the items contained in the workbook. Also each workbook has 25 sheets each, that contain the data of the items to be priced. Depending on the number of items in the style, I don't usually have to use all 25 sheets, but have them incase I need them. Each workbook also has a sheet named 'Costing' that is a compilation of all the info from the 25 worksheets. All of these workbooks use Vlookup that pulls from one MasterPriceList. I've created a new workbook for each customer, that contains all the info from the Costing Sheet for each style (workbook) the customer has.

Is there anyway, that when I change the prices in the MasterPriceList, that the new workbook I've created, that contains the Costing Sheet for each style for a customer, will automatically update, when I open it. Right now, I have to physically open each style (workbook) to be able to see the change made to the MasterPriceList. Hope all of this makes sense.

Thanks in advance.

 

[Joe4]

Welcome to the Board!

I have been watching this thread for replies before adding in my 2 cents, but see that you haven't gotten any responses yet.

What you are describing is a relational datbabase (many sheets/sets of data related to one another). While many people try to use Excel as a relational database, that is really not what it was designed for. You can make it work, but it is often clunky, inefficient, and slow. It would be better to use a relational database program, such as Microsoft Access, SQL, MySQL, or Oracle. They were designed for these purposes, and handle it much better (you simply set up relationships between your tables instead of using a ton of VLOOKUP functions).

To create a well-designed relational database using takes some knowledge on the concepts of Relational Databases, especially the Rules of Normalization (Relational Database Normalization Basics | Database Solutions for Microsoft Access | databasedev.co.uk), so it usually takes some educating yourself before tackling such as task (or enlisting the help of someone knowledgeable in the area). So it usually requires a bit of up-front education and work, but you will find that it pays off in the long run.

 

[hunter122976]

Welcome to the Board!

I have been watching this thread for replies before adding in my 2 cents, but see that you haven't gotten any responses yet.

What you are describing is a relational datbabase (many sheets/sets of data related to one another). While many people try to use Excel as a relational database, that is really not what it was designed for. You can make it work, but it is often clunky, inefficient, and slow. It would be better to use a relational database program, such as Microsoft Access, SQL, MySQL, or Oracle. They were designed for these purposes, and handle it much better (you simply set up relationships between your tables instead of using a ton of VLOOKUP functions).

To create a well-designed relational database using takes some knowledge on the concepts of Relational Databases, especially the Rules of Normalization (Relational Database Normalization Basics | Database Solutions for Microsoft Access | databasedev.co.uk), so it usually takes some educating yourself before tackling such as task (or enlisting the help of someone knowledgeable in the area). So it usually requires a bit of up-front education and work, but you will find that it pays off in the long run.

Thank you for your reply!

I kind of assumed I needed to be using something else, other than excel for what I'm doing. The bad thing is, there's so much data that would have to be put into one of these other programs and unfortunately, I don't have a clue how to use any of those. I do have access, so I guess I can give it a try. Is there a way to import any of the info I already have into something like access?

Again, thanks for your reply.

 

[Joe4]

Yes, importing data into Excel from Access is pretty easy. But it might be necessary to do some data clean-up first, if the data is not in a normalized form.
Working with data that is not normalized in Access can be a very frustrating experience. Set it up right, and things work great. But if you don't, it can lead to a lot of headaches.

There are lots of good tutorials, documents, and videos that you can find on-line to help. It does require a bit of upfront learning first (not just using Access, but relational database design). It took me a few months the first time I created a database in Access (though it was doing a myriad of tasks), but it ended up being something that they have been using for 20 years now for invoicing and mass mailings to clients.

You may be able to speed along the process if you have someone who can help (a consultant who specializes in database design, or someone else familar with creating databases in Access).

 

[severynm]

Would it not be possible to loop through all the files in a folder, open them, and save them (thus forcing the formula recalculation)? That being said, I agree with Joe too - once you pass a certain point with number of sheets and workbooks all linking together, excel is not the correct tool for the job.