Automatically update

hunter122976

New Member
Joined
Mar 31, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,405
Messages
5,636,102
Members
416,898
Latest member
imsorrymen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top