We recently got a new client that he wants us to design their product catalogue. Their previous product catalogue was designed internally in the company. It is a 500 pages detailed catalogue with over 5000 products and each product has its table with sizes and prices.
He sent us all the files from that catalogue so we can figure out how they set the catalogue up.
The method they used is, export all products with their values from the Software they use to keep track of their inventory. Then they created around 600 smaller workbooks for each individual table they wanted to create. Each small workbook is set up to auto update its values from the "master" XLSX file the software exported. That way they drag and dropped the small workbooks (1 to 10 lines each) to inDesign and create tables there.
So each time someone exported the XLSX file from their inventory software and gave it to the designer, the designer somehow updated updated all the small workbooks automatically and Adobe inDesign updated the values on the catalogue.
My questions are:
- The small workgroups read their values from the "master" xlsx file by using a path in their cells. See image here https://i.imgur.com/Qs71Hpv.png Is there a way to open all 600 smaller workbooks and change the path of the "master" xlsx they are reading? The path is present only in the ΤΙΜΗ column but every workbook can have from 1 to 20 rows.
- My second question is, to update the values on each small workbook you have to open, save, close it. Is there a bulk method to update multiple workbooks without manually opening each one?
If someone could help me figure these out, as i am not very familiar with excel it self, it will save me a lot of time and errors.
Thank you in advance.