Automatically adding rows to one table based on cell value of another table

D

dallen8028

New Member
Joined
Jan 28, 2013
Messages
48
I am looking to Automatically add rows to one table based on cell value of another table. Example: If [Table 1 [cell A1]]="Maintenance" then [Table 2] will copy [Table 1 [Cells B1,C1]] to [Table 2[cells F1,G1]] then auto TAB to add a new row to [Table 2] for the next "Maintenance" match. As entries are input to Table 1 that have "Maintenance" in cell A1 the process continues. I would upload the two tables but I do not have upload privileges yet. I apologize for any confusion ahead of time. Please advise. Thanks in advance.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
So you must have these two tables on different sheets.

When I refer to Tables I would say Table1 column 1
Not Table1 A1

And from both of your descriptions both Tables must start in column A of your sheet.

And normally row 1 of your Table would be your Table Header.

Can you clear that up for me please.

Are we sure were talking about Excel Tables or Excel Sheets
 
Last edited:
Upvote 0
And when I say Column 1

I mean column 1 of the Table not column 1 of the sheet.
A Table can be put anyplace on a sheet so column 1 of the Table may mean Column 14 of the sheet.
 
Upvote 0
My Aswer Is This said:
So you must have these two tables on different sheets.

When I refer to Tables I would say Table1 column 1
Not Table1 A1 - You are correct

And from both of your descriptions both Tables must start in column A of your sheet. - No, both tables on same sheet with the headers on the same row.

And normally row 1 of your Table would be your Table Header. - This is correct, migrate all Row 1 to Row 2.

Can you clear that up for me please. - Yes

Are we sure were talking about Excel Tables or Excel Sheets- Excel Tables
Click to expand...

Do the answers help?
 
Upvote 0
Your subject Title:
Automatically adding rows to one table based on cell value of another table​

So are you saying you want this to happen when
Maintenance​
is entered in Table 1 Range A1

So each time you will always be entering the same value into the same cell.

Or do you mean when you enter
Maintenance​
into column 1 of Table1

So first time it would be Table1 Column1 row 1
Next time it would be Table1 Column1 row 2
 
Upvote 0
My Aswer Is This said:
Your subject Title:
Automatically adding rows to one table based on cell value of another table​

So are you saying you want this to happen when
Maintenance​
is entered in Table 1 Range A1

So each time you will always be entering the same value into the same cell.

Or do you mean when you enter
Maintenance​
into column 1 of Table1

So first time it would be Table1 Column1 row 1
Next time it would be Table1 Column1 row 2
Click to expand...

I was trying to simplify with above. Sorry for the confusion. Here are images of the two tables.

Table One

<colgroup><col style="mso-width-source:userset;mso-width-alt:3770; width:81pt" width="108" span="4"> <col style="mso-width-source:userset;mso-width-alt:3258; width:70pt" width="93" span="2"> <col style="mso-width-source:userset;mso-width-alt:8308;width:179pt" width="238"> <col style="mso-width-source:userset;mso-width-alt:3258;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:4538; width:98pt" width="130" span="2"> </colgroup><tbody>
[TD="class: xl85, width: 1209, colspan: 10"]Travel History[/TD]

[TD="class: xl66, width: 108"]Check Out Date[/TD]
[TD="class: xl66, width: 108"]Chek Out Time[/TD]
[TD="class: xl67, width: 108"]Driver[/TD]
[TD="class: xl67, width: 108"]Event[/TD]
[TD="class: xl67, width: 93"]Start Mileage[/TD]
[TD="class: xl68, width: 93"]Stop Mileage[/TD]
[TD="class: xl68, width: 238"]Total Mileage[/TD]
[TD="class: xl68, width: 93"]Check in Date[/TD]
[TD="class: xl67, width: 130"]Check in Time[/TD]
[TD="class: xl73, width: 130"]Total Time Used [/TD]

[TD="class: xl70, align: right"]8/7/2018[/TD]
[TD="class: xl71, align: right"]15:58[/TD]
[TD="class: xl65"]Sampuel, Jose[/TD]
[TD="class: xl65"]Maintenance[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl77, align: right"]103,805[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl69, align: right"]8/9/2018[/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl76, align: center"]#################[/TD]

[TD="class: xl78, align: right"]8/7/2018[/TD]
[TD="class: xl80"] [/TD]
[TD="class: xl81"] [/TD]
[TD="class: xl82"] [/TD]
[TD="class: xl79, align: right"]0[/TD]
[TD="class: xl74, align: right"]103,805[/TD]
[TD="class: xl77, align: right"]0[/TD]
[TD="class: xl83"] [/TD]
[TD="class: xl84"] [/TD]
[TD="class: xl72, align: center"]#################[/TD]

</tbody>

Table Two


<colgroup><col style="mso-width-source:userset;mso-width-alt:3467; width:75pt" width="99" span="3"> <col style="mso-width-source:userset;mso-width-alt:3537;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:4933;width:106pt" width="141"> </colgroup><tbody>
[TD="class: xl66, width: 99"]Last Service Date[/TD]
[TD="class: xl67, width: 99"]Service Type[/TD]
[TD="class: xl67, width: 99"]Next Service Date[/TD]
[TD="class: xl67, width: 101"]Mileage[/TD]
[TD="class: xl68, width: 141"]Service Cost[/TD]

[TD="class: xl69, align: right"]8/7/2018
[/TD]
[TD="class: xl65"]
[/TD]
[TD="class: xl65"]
[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl70"] [/TD]

[TD="class: xl71, align: right"]8/7/2018[/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl72, align: right"]0
[/TD]
[TD="class: xl73, align: right"]0[/TD]

</tbody>

I want the service date and mileage to be copied from table one to table two any time the event column = maintenance and then I want table two to automatically add a row for the next time table one has a maintenance entry. Does that make better sense?

I didn't think I could copy and paste or I would have done it before. Again sorry for the confusion.
 
Upvote 0
You can try this:
You really do not need to add a new row to table1
Any time you enter a value in a table row
And then enter a new value in the next row a new row is automatically added.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter Maintenance into column 4 of Table1 the script will do what you asked for
Copy parts of this row to Table2

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  12:59:40 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
    End If
End If
End Sub
 
Last edited:
Upvote 0
If you really think the script needs to add a new row to Table1 then use this script:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  4:45:14 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
       ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
      
    End If
End If
End Sub
 
Upvote 0
My Aswer Is This said:
If you really think the script needs to add a new row to Table1 then use this script:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/10/2018  4:45:14 AM  EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
If Not Intersect(Target, [Table1[Column4]]) Is Nothing Then
    If Target.Value = "Maintenance" Then
        ans = [Table2[Column4]].Columns.Rows.Count + 2
        Cells(Target.Row, [Table1[Column1]].Column).Copy Cells(ans, [Table2[Column1]].Column)
        Cells(Target.Row, [Table1[Column5]].Column).Copy Cells(ans, [Table2[Column4]].Column)
       ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
      
    End If
End If
End Sub
Click to expand...

Thank you so much for your hard work. I have not applied your script yet as I do not know how to read script that well and do not know if it adds a row to table one or table two. I do not want to auto add a row to table one but to table two each time column 4 in table one reads Maintenance. Is that possible?
 
Upvote 0
Use the script in Post 7

It should do what you want.
If not tell me what it did wrong.

You cannot know what it does till you test it.

Try it on a copy of your workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Extending Excel Table
2
Replies
12
Views
243
Peter_SSs
Peter_SSs
M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
51
Edgar_
Edgar_
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
385
Skyybot
Skyybot
H
  • Question Question
Positioning active cell when adding table rows.
Replies
3
Views
183
myall_blues
myall_blues
willow1985
  • Solved
Linking 2 identical tables in the same workbook so when one updates the other updates
2
Replies
18
Views
409
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top