CODEWITHSUNDEEP
excelexcel-formula
szab.kel
szab.kel

Reputation: 2536

Excel apply formula after pasting table

Is there a way to create an excel template, which automatically applies a formula if a certain column is inserted? The use case would be:

  1. User copies a table from a desktop program (CSV or Tab separated)
  2. Pastes the table into the excel template
  3. Somehow the column's header gets matched (header's name is fixed)
  4. Multiply the column values with a fixed value (like 1.25)

The problem is that the column could be in different indexes and the formula should apply to all the rows, not just fixed ones, but my main problem is how can I get a copy-paste event and find the column.

Edit.: I can only do this with VBA (never used it before)?

Upvotes: 1

Views: 127

Answers (1)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

Assuming the column header is constant you could insert a table with "dummy" headers and a calculated column using the header name as a reference enter image description here

In the example I have put a formula in the Calc column. I've put the Calc in column G assuming your maximum import columns are six and because it's good practice to do your calculations from left to right.

You could move the Calc to column A and reduce the "dummy" columns to just 1 with whatever your desired heading is.

To make a template, delete row 3 and clear out the dummy data, just leaving the calculation(s) and save as a .xltx in your profile template folder (wherever that may be in your versions of Windows/Office).

When you paste in your import data the table will extend down and copy the formula(s) automatically. If you have moved your calculation(s) to the left of the table, the table will resize to the right to accommodate all your imported columns.

While the MyHeader column in your template may be over-written, if it can still be found in `Table2[#Headers] the formula will still work.

Upvotes: 1

Related Questions