Need help with anything about formulas, formatting, shortcuts, pivot tables etc.

[abdulkharij]

I have the following table in sheet-1 (master table) from an excel workbook. I want an excel formula that can allow me to automatically add a row to similar table in sheet-2 if I add it to the main table in sheet-1.

Column Name in sheet2 is linked to column Name in sheet1.


Example: If I add a row with the name "Jane" in sheet-1 it should automatically be added to similar table in sheet-2

Sheet1 Sheet2
A B C D A B C D
Name Hours Rate Total Name Hours Rate Total
jhon 35 10 350 jhon 35 10 350
David 20 11 220 David 20 11 220
Mark 50 12 600 Mark 50 12 600
Hellen 40 11 440 Hellen 40 11 440
Iren 40 13 520 Iren 40 13 520
Nicole 39 14 546 Nicole 39 14 546
Thank you,

 

[Somendra Misra]

Hi Abdul,

Kindly answer some queries:

1. Are you going to alter row numbers in main table Say David is row no. 4 are you going to make row 6 as David in future?

2. How the data will be entered in main table?

3. are you going to update data which already got entered in the main table, and if got updated should it also gets reflected in other table?

Regards,

 

[abdulkharij]

Rows layout may change.
I will enter number manually in the mean table (Sheet1).
I will update data in sheet1 every month.
So I want only that row (with new names) be added to sheet2 every time I manually add them to Master sheet1
Example: if I want to insert Row with name "Jane" under David in sheet1 I want it to be automatically insert under David in sheet2

 

[Somendra Misra]

Ok...

Can you upload a sample file with both table structure?

Regards,

 

[abdulkharij]

Please check the attached file for an excel workbook

 

[Somendra Misra]

@abdulkharij

See this file. I had changed your main table to Table and than created a pivot of it. Now adding or changing data will reflect in the Pivot. I would suggest you to add a unique id to each record.

Regards,

 

[abdulkharij]

Thank you for your help, but I am not allowed to use Pivot tables. Is there any way where I can just do it without creating a Pivot table?

 

[abdulkharij]

Also I was giving this Code by someone, but I don't know why it didnt work
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If LenB(Target.Value) > 0 Then
If Not Intersect(Target, Range("A2:A7")) Is Nothing Then
If Target.Row = UsedRange.Rows.Count Then
If UsedRange.Rows.Count > Sheet2.UsedRange.Rows.Count Then
Sheet2.Cells(Sheet2.UsedRange.Rows.Count + 1, 1).Value = Target.Value
End If
End If
End If
End If
End Sub

 

[Somendra Misra]

Also I was giving this Code by someone, but I don't know why it didnt work
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If LenB(Target.Value) > 0 Then
If Not Intersect(Target, Range("A2:A7")) Is Nothing Then
If Target.Row = UsedRange.Rows.Count Then
If UsedRange.Rows.Count > Sheet2.UsedRange.Rows.Count Then
Sheet2.Cells(Sheet2.UsedRange.Rows.Count + 1, 1).Value = Target.Value
End If
End If
End If
End If
End Sub

Will update something tomorrow.

Regards,