Auto add row Macro

Hi,

I am working on a project now in which I have a excel database which contains results pulled from an online survey. The database contains a results tab and several tabs with automated analysis. As I receive new results I copy/paste the row into the results tab and the table updates itself. My problem is I am using a "Result ID" as a primary key for each result and when I add an additional row I have to go through the tabs and expand each table that has "Result ID" as a field by one (or however many new results were added). I would like to make a macro that automatically updates those specific tables as I add additional rows. Please help

when I add an additional row I have to go through the tabs and expand each table that has "Result ID" as a field by one (or however many new results were added)

A macro doesn't seem to be the required solution, but maybe I'm not understanding something here.

From the way you have described your problem, your formulas in the analyses sheets have to be adjusted for new data added to the results tab.

You need to create a dynamic named range for each of the tables and reference the named range in your formulas. By do so they will automatically update when new data are added.

A more accurate answer may be possible if you upload a sample workbook.

My formulas don't need to be updated every time I add a new result because I use named ranges and have all my tables formatted as tables so they automatically fill the formulas down. However the formulas in the 4 tables reference the "results ID" value, and because it does not automatically add another row when I add a new result to the results tab, i have to manually add a new row to each table.

I attached a sample database. It includes the 3 tabs that are involved in this problem as well as the named ranges and table formulas. On the results sheet, there is a test new result to be copy/pasted right below the table. This should automatically add the row to the table and autofill the salesman ID column. The results ID column does not autofill (I assume because it isnt a formula).

Also on the other two sheets, the tables "Salesman Satisfaction Results," "Email Table," and "Unsatisfied Customers" all require updating as new results are added. Notice that they do not update until the new "result ID" (4) is added.

I want a macro that will automatically add a new result ID in the results sheet as well as add a new one in the other 3 tables as I add a new result.

Although you use named ranges they static and not DYNAMIC, thus everytime you add more data the new data falls outside of the named range.

If you use a dynamic named range, then you should get the results you want.

I am not familiar with dynamic named ranges. I did some research and tried creating a dynamic named range. I used the formula

=OFFSET(Results!$A$2,0,0,COUNTA(Results!$A:$A),1)

in the refers to line. The result ID did not carry down when I pasted a new row. Is there additional steps I am missing? Do I need to do something to the data in "Results ID?"

The result ID did not carry down when I pasted a new row. Is there additional steps I am missing? Do I need to do something to the data in "Results ID?"

You have quite a number of static named ranges in your workbook. Not all of them necessarily need to be a dynamic named range, but . . .

In each range that is named, if you expect to add data, then all of the static named ranges must be converted into a dynamic named range. Then . . .

Each formula that uses a named range in one of its arguments must be updated with the new name, unless . . .

You keep the existing name and just modify the "Refers To:" field to a formula for a dynamic named range.

The example dynamic named range you gave is a legitimate named range. Did you mean for it to be just one column wide? (last argument in the formula specifies the range is only one column)

Copy and pasting data into a row does not have any effect on dynamic named range other than to force it to expand down another row.

HTH,

Palmetto

I'm not really understanding how the dynamic ranges work. However, I did try using 1 for the number of columns as well as 18 (which is the total number of columns in my results table). Neither seemed to work

If I understand right, naming the entire table (or just the column you want to carry down?) as a dynamic range will force it to continue the pattern (1,2,3,...) when a new row is created. Is that correct?

I'm not sure what my formula is missing.

In the other tables that reference that "result ID," the result ID is not derived from a formula, it is simply typed in. So I am wondering if those will need to be formulas to update as new results are added.