help wirh Excel formula

A

abdulkharij

New member
Joined
Mar 6, 2015
Messages
4
Reaction score
0
Points
0
I have similar tables in sheet1 and sheet2. I want Rows and data, with new names, to be added/deleted automatically to table in sheet2 if I manually add/delte them to sheet1.
SHEET1
SHEET2
A
B
C
D
G
H
J
K
1
Name
Hours
Rate
Total
5
Name
Hours
Rate
Total
2
jhon
35
10
350
6
jhon
35
10
350
3
David
20
11
220
7
David
20
11
220
4
Mark
50
12
600
8
Mark
50
12
600
5
Hellen
40
11
440
9
Hellen
40
11
440
6
Iren
40
13
520
10
Iren
40
13
520
7
Nicole
39
14
546
11
Nicole
39
14
546
Thank you
​
 
Last edited:
Perhaps something like this, im make formula cover the data until 500 rows and using array formula

see u
 

Attachments

  • Book1-Example.xlsx
    15.4 KB · Views: 23
I did drug the whole table in sheet1 to move Cell contents, and that affected the fomula and didnt work properly. is there any way to avoid this when I dug table in sheet1?

druging table in sheet2 didn't affect the fomula wich is good :)
 
I was giving this code, and it work very good, but the only problem with it is that when you add row in the of the table-shate1 it goes to the last row in table-sheet2. Could you help me fix it, so when I insert a row in sheet1 should go to the same spot in sheet2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If TypeName(Target) = "Range" Then
If LenB(Target.Cells(1, 1).Value) > 0 Then
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.SpecialCells(xlCellTypeLastCell).Row = UsedRange.Rows.Count Then
If UsedRange.Rows.Count > Sheet2.UsedRange.Rows.Count Then
For Each cell In Target.Cells
If Not Intersect(cell, Range("A:A")) Is Nothing Then Sheet2.Cells(Sheet2.UsedRange.Rows.Count + 1, 1).Value = cell.Value
Next
End If
End If
End If
End If
End If
Set cell = Nothing
End Sub





Thank you
 
You must log in or register to reply here.
Back
Top