Hello
I want to create a database from Excel files in PDM.
There would be formulas between these files (VLOOKUP for example).
The question I ask myself is how do these links between files work when they are archived?
If I force " get the latest version " on these files, the formulas will follow if I open a file (only 1 file) or do I have to open all the files to update the values?
If not, with you an idea on how to make a database in PDM (with formulas between files that update)?
Thank you for your help
Hello
If I understood correctly, you will have at least 2 Excel files and the second one will look for values in the first one?
If this is the case, the operation should be quite similar to what a solidworks assembly does: when opening the file it will go and get the latest version (if your chest is set like this). On the other hand, the updates will only be done when the file is opened: Epdm will not update your file containing the vlookups on its own (like EPDM which does not update SW assemblies that use a modified part).
On the other hand I don't think that EPDM handles references inside Excel (I could be wrong on this point, I know that there is an installable add-in for Office, it may scan external references)).
If the Excel references are not managed by EPDM, the risk is that your file with the vlookup points to an Excel file in your local view and that it does not load the latest version.
1 Like
Hello
Never tested but maybe by linking the files (via the content tab then custom references)
Normally, if you manage like SW files by getting the version before opening, it should follow.
Hello
It is better to import the data and transform it rather than putting formulas that reference cells from another file:
From there it is possible to manage the synchronization: when opening the file, or on request:
No problems with PDM working like this.
Note: my sources were in a folder whose content was always at the latest version and copied locally to users (see pdm settings cache option)
2 Likes
Hello
I use this method when the databases are ' fixed ' but doesn't work in the case of a formula link.
In my case: I have the workbook A, open in which I choose my parameters (example cell A1). I have a binder B that is closed and uses cell A1 of binder A to perform a calculation whose result is in cell C2 (binder B). Workbook A retrieves cell C2 from workbook B as the result of the calculation.
These links work well when workbooks are open, but do not refresh when Workbook B is closed.
I'm going to make I think a macro that opens the workbook B (hidden) to update the calculations and then close it.
Thank you for your answers
1 Like
Hello
Wouldn't it be wiser to have only one file with several sheets than to manage several files (especially if it makes the ping pong between the files...)?
1 Like
Hello @froussel,
Indeed it's easier to have everything in the same file but I can't have a single file because of the complexity and heaviness of each " child " file. In my example I took 2 binders but in reality I have more than fifty (which I don't open all of them fortunately but only the ones I need according to my chosen configuration).
Thank you all the same