Automatically add a value to a table using a macro

[Snapshot84]

Hi

I really need some help either writing a macro or finding another way to have my database automatically fill data in a row when new values are added.

I am working with excel 2007, I have a database where I add information pulled offline. I copy/paste the values into a new row under the "Results" table. I have a primary key ("Results ID") that just consists of typed numbers (1,2,3,...) used as a unique number for each result. In my database I have several analysis tables which include fomulas for calculations and to relate to and pull the original data from the "results" sheet. These analysis tables also reference the primary key, and contain the same amount of rows as the "results" sheet.

My problem comes in when i add new results to the table. When I copy/paste the new result under the last record the table automatically "attaches" that new result to be included with the rest. It also automatically fills the formulas from other columns down as well. It does not however fill the primary key values down.

I would like some help on writing a macro to make it do this as new rows are added. I would also like the macro to cause the primary key columns in the 3 analysis tables to automatically expand as rows are added to the "results" table.

Please help me.

 

[jbeaucaire]

It sounds like you're using Excel's LIST feature so that new data added to a bottom of the LIST automatically expand the bounded region to include the new data? Any formatting and formulas from the row above are copied down into the new row at the same time the expansion occurs.

If you're wanting the primary key value to automate, all you need do is use a formula to create it. You indicated it is a simple numeric series (1,2,3,...).

So the first key needs to be manually entered as 1 (let's say that was A2). But the next value you should enter as a formula with =A2+1

Now Excel's LIST functionality can number your primary key column, too, because of the formula.

 

[Snapshot84]

I was able to use a similar formula to make that work (I had to COUNT the values from one of the colums that has data added when a new row is pasted instead of just doing 1, A2+1, A3+1,...) So now the data in the "Results" sheet automatically fills all columns as new results are added.

I still have a problem making the tables from different sheets automatically expand another row when the "Results" sheet gains a row.

Any advice?

 

[jbeaucaire]

I still have a problem making the tables from different sheets automatically expand another row when the "Results" sheet gains a row.

There's not enough information here to advise. Sorry.

 

[Snapshot84]

I have several sheets in my workbook. The Results sheet contains the raw data (Customer Surveys) pulled from an online website. As stated previously I have assigned a primary key called "Results ID" to each of these results. There are 2 other sheets in the workbook which use this Result ID as a reference to display certain data fields as well as a reference for performing calculations.

As I add new results, currently, I have to go through each sheet and manually add an additional value to the Results ID column in order to expand the tables to include and represent all results. I would like to find some way to make these analysis tables on the respective sheets automatically expand as the new results are posted on the Results sheet.

Whether this is some time of formula or a macro to run everytime I add additional rows, I would like to eliminate the need to manually add data in the relating fields when new data is added.

 

[jbeaucaire]

The simplest idea that comes to mind is create your secondary tables and have them draw over the Result ID using a basic:

='Customer Surveys'!A2
='Customer Surveys'!A3
='Customer Surveys'!A4
...etc

These formulas should go down far past the number of actual rows so that as new items are added in the surveys, the new ID simply appears in the new list.

The rest of the formulas in those secondary tables should now start with a check to make sure the value in the first cell is something other than 0...

=IF($A2=0, "", YourFormula)

This should keep new data appearing properly.

Since the column A values go far past the number of needed rows, you may find the resulting zeros unsightly, so go to Tools > Options > View > [ ] Zero values and uncheck that to not show zeros on that one sheet.