I am in charge of creating the nomenclatures of the machines that we design in our company.
My objective is to create a link between the outgoing solidworks BOMs, with my Excel table including all the screws (Type / Ø / Length / Material / Supplier / QUANTITY) present in my stock.
I don't know if I made myself understood well but in plain English it would give its:
Machine "X": TH M6x10 Screw (x10) / M6 Nut (x10)
==> Stock table (Excel): Automatically remove the emblem of the screws according to a command of x4 Machine "X".
You can use the BOM saved in Excel or use it as an OLE object in other applications. The saved file is not chained to the BOM in the drawing; the editions of one do not affect the other.
Well my problem now is that I don't see at all how to make the link between my Stock / Debit cells knowing that they are located on two separate excel files. Something I'd like to keep in this form (with an Excel Stock and an Excel Nomenclature)
In any case, it's cool to look into my problem, thank you! :)
Creating a link between two sheets or between two binders is actually very simple. In fact, every time you create a formula by inserting references into it, you make connections between different cells. But we are not talking about links as such, because the different references are on the same sheet. When you start a formula (with the = sign ) and point to a cell in another sheet (by selecting the sheet with the tab), the link is created automatically. The difference you will notice in the formula is the appearance of the name of the sheet that has been linked (source sheet) next to the reference. An example: =D5*PRICE! D7.
Here's an example of a link between two sheets in the same workbook:
In this Excel workbook there are 2 sheets ("Exchange Rate" and "Conversion")
In the "Exchange rates" sheet there is a table containing rates for different currencies.
In the "Conversion" sheet we will create formulas to calculate the price in local currency, directly using the respective rate found in the "Exchange Rates" sheet . In our example, the rate for Italian lira. The "Exchange Rate" sheet is the source sheet in this case .
In the "Conversion" sheet (destination sheet) we will create the formula. To convert in this case you will have to multiply the price in Italian Lira (LIT) by the respective rate in the other sheet. The following image illustrates the formula.
N.B. The expression 'Exchange Rate'! Indicating the sheet name of the linked cell is automatically added to the formula. To validate the formula, press <Enter> after selecting the cell to link, this automatically returns you to the destination sheet.
You can also link data that is in different workbooks. The principle for creating a link between workbooks is the same as for sheets, with the difference that you will have to look for the cell to be linked by going through the Window menu to display the source sheet.
When you create bindings between workbooks, the Bindings ... in the Edit menu activates
This command opens the following dialog box that allows you to open the source file, change the source of the link, or update the link.
Be careful when moving an Excel workbook that contains S bindings
Thank you for the link but I can't do exactly what I want.
I would like to make the link between two Quantity columns (Stock file / Debit file) so that Excel automatically knows how to find the corresponding references (Vis TH or TCLZ) between stock and debit. As soon as the references match, the subtraction can take place.
Attached is an example of the Stock table with the screws present in the Excel BOM file (Solidworks) in yellow.
However, this does require knowing how to master Excel formulas.
You can do anything you want with Excel or even better Access whose in cells put conditions with multiple nests in a cell including cell A123 which is in the sheet quichemolle_V12 . You can even do even more Sioux stuff with VBA in excel.