Automatically add a value to a table using a macro

Snapshot84

New Member
Joined
Aug 28, 2009
Messages
7
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Aug 28, 2009
Messages
7
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?
 

Snapshot84

New Member
Joined
Aug 28, 2009
Messages
7
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

Forum statistics

Threads
1,082,382
Messages
5,365,129
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top