Hi.
I´m a newbie at this forum.
Could you help me with my spreadsheet ?
First, let me explain how the spreadsheet works (please, check at the end of this post).
It is a budget worksheet of items for constructions.
We quote the prices of each item with several suppliers, so as we receive the proposals, we create new columns (Supplier 1, Supplier 2, Supplier 3, etc.).
Prices are compared between a reference table and median of suppliers' prices. The "adopted value" column is the lowest value between these two.
Therefore, the "Quotation quantity", "Median" and "Coefficient of variation." Columns depend on the number of supplier columns.
Depending on the size of the item list, we may have more than 50 different suppliers.
As other colleagues work with this same spreadsheet, it would be important that the formulas for these 3 columns start from the VBA itself.
My requests are:
1) I would like a VBA code to automatically update the formulas of these 3 columns (for the number of lines equal to the number of items) when creating new supplier columns, as well as to drag the formulas of these 3 columns to the lines below, when new items are added.
2) I think of creating a button for the user to add new columns keeping the same formatting as the last column. In the example, when clicking on this button, the column for Supplier 6 would be created, with empty data, but with the same format as the column for Supplier 5.
I'm a beginner in VBA, I've managed to automate some things in this spreadsheet, but I'm stuck in that part.
Thank you for your help.
Here is the plan:
I´m a newbie at this forum.
Could you help me with my spreadsheet ?
First, let me explain how the spreadsheet works (please, check at the end of this post).
It is a budget worksheet of items for constructions.
We quote the prices of each item with several suppliers, so as we receive the proposals, we create new columns (Supplier 1, Supplier 2, Supplier 3, etc.).
Prices are compared between a reference table and median of suppliers' prices. The "adopted value" column is the lowest value between these two.
Therefore, the "Quotation quantity", "Median" and "Coefficient of variation." Columns depend on the number of supplier columns.
Depending on the size of the item list, we may have more than 50 different suppliers.
As other colleagues work with this same spreadsheet, it would be important that the formulas for these 3 columns start from the VBA itself.
My requests are:
1) I would like a VBA code to automatically update the formulas of these 3 columns (for the number of lines equal to the number of items) when creating new supplier columns, as well as to drag the formulas of these 3 columns to the lines below, when new items are added.
2) I think of creating a button for the user to add new columns keeping the same formatting as the last column. In the example, when clicking on this button, the column for Supplier 6 would be created, with empty data, but with the same format as the column for Supplier 5.
I'm a beginner in VBA, I've managed to automate some things in this spreadsheet, but I'm stuck in that part.
Thank you for your help.
Here is the plan:
| Teste ENG.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 2 | Item | Description | Unity | Quantity | Quant. of quotation | Median | Adopted value (table or median of quotations) | Coeff. of variation | Supplier prices | |||||||
| 3 | Table of reference | Supplier 1 | Supplier 2 | Supplier 3 | Supplier 4 | Supplier 5 | ||||||||||
| 4 | 5 | 5 | 4 | 4 | 5 | 3 | ||||||||||
| 5 | 1 | Lamp XYZ | un | 100 | 4 | 63,5 | 60 | 46,56% | 60 | 96 | 44 | 83 | 34 | |||
| 6 | 2 | Structural adhesive 000 | un | 200 | 5 | 76 | 76 | 57,13% | 80 | 11 | 76 | 91 | 99 | 43 | ||
| 7 | 3 | Reactor 111 | un | 100 | 4 | 64,5 | 55 | 31,37% | 55 | 97 | 50 | 54 | 75 | |||
| 8 | 4 | Junction box 222 | un | 50 | 3 | 80 | 75 | 10,82% | 75 | 70 | 87 | 80 | ||||
| 9 | 5 | Cable 12345 | m | 1000 | 5 | 51 | 51 | 20,80% | 60 | 51 | 45 | 64 | 58 | 37 | ||
Planilha1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I4:N4 | I4 | =SUBTOTAL(102,I5:I9) |
| E5:E9 | E5 | =IF(ISNUMBER(I5),MAX(3,COUNT(J5:N5)),COUNT(J5:N5)) |
| F5:F9 | F5 | =IF(A5<>"",IF(AND(E5>0,ISNUMBER(MEDIAN(J5:N5))),ROUND(MEDIAN(J5:N5),2),"N/A"),"") |
| G5:G9 | G5 | =IF(A5<>"",IF(ISNUMBER(I5),MIN(I5,F5),""),"") |
| H5:H9 | H5 | =IF(COUNT(J5:N5)>1,STDEV(J5:N5)/AVERAGE(J5:N5),"N/A") |
