Update formulas (same row) when adding columns and button to create column with same format of the last one

Quest_

New Member
Joined
Jul 7, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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:

Teste ENG.xlsx
ABCDEFGHIJKLMN
2ItemDescriptionUnityQuantityQuant. of quotationMedianAdopted value (table or median of quotations)Coeff. of variationSupplier prices
3Table of referenceSupplier 1Supplier 2Supplier 3Supplier 4Supplier 5
4554453
51Lamp XYZun 100463,56046,56%6096448334
62Structural adhesive 000un 2005767657,13%801176919943
73Reactor 111un 100464,55531,37%5597505475
84Junction box 222un 503807510,82%75708780
95Cable 12345m10005515120,80%605145645837
Planilha1
Cell Formulas
RangeFormula
I4:N4I4=SUBTOTAL(102,I5:I9)
E5:E9E5=IF(ISNUMBER(I5),MAX(3,COUNT(J5:N5)),COUNT(J5:N5))
F5:F9F5=IF(A5<>"",IF(AND(E5>0,ISNUMBER(MEDIAN(J5:N5))),ROUND(MEDIAN(J5:N5),2),"N/A"),"")
G5:G9G5=IF(A5<>"",IF(ISNUMBER(I5),MIN(I5,F5),""),"")
H5:H9H5=IF(COUNT(J5:N5)>1,STDEV(J5:N5)/AVERAGE(J5:N5),"N/A")
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
I have modified your layout and placed your data in a table. The formulas were changed slightly to take advantage of the table properties. The formulas will be copied down when new rows are added.
If you insert columns to the left of the last column (LC) it will mocify the columns E, F, H formulas that need a span of columns.

Book2
ABCDEFGHIJKLMNO
2ItemDescriptionUnityQuantityQuant. of quotationMedianAdopted value (table or median of quotations)Coeff. of variationSupplier prices
3554453
4Column1Column2Column3Column4Column5Column6Column7Column8Ref TableSupplier 1Supplier 2Supplier 3Supplier 4Supplier 5LC
51Lamp XYZun100463.56046.56%6096448334
62Structural adhesive 000un2005767657.13%801176919943
73Reactor 111un100464.55531.37%5597505475
84Junction box 222un503807510.82%75708780
95Cable 12345m10005515120.80%605145645837
Sheet4
Cell Formulas
RangeFormula
I3I3=SUBTOTAL(102,Table2[Ref Table])
J3J3=SUBTOTAL(102,Table2[Supplier 1])
K3K3=SUBTOTAL(102,Table2[Supplier 2])
L3L3=SUBTOTAL(102,Table2[Supplier 3])
M3M3=SUBTOTAL(102,Table2[Supplier 4])
N3N3=SUBTOTAL(102,Table2[Supplier 5])
E5:E9E5=IF( ISNUMBER(I5),MAX(3,COUNT(Table2[@[Supplier 1]:[LC]])),COUNT(Table2[@[Supplier 1]:[LC]]))
F5:F9F5=IF(A5<>"",IF(AND(E5>0,ISNUMBER(MEDIAN(Table2[@[Supplier 1]:[LC]]))),ROUND(MEDIAN(Table2[@[Supplier 1]:[LC]]),2),"N/A"),"")
G5:G9G5=IF(A5<>"",IF(ISNUMBER(I5),MIN(I5,F5),""),"")
H5:H9H5=IF(COUNT(J5:N5)>1,STDEV(Table2[@[Supplier 1]:[LC]])/AVERAGE(Table2[@[Supplier 1]:[LC]]),"N/A")
 

Watch MrExcel Video

Forum statistics

Threads
1,114,032
Messages
5,545,626
Members
410,696
Latest member
JTrehan
Top