How to fetch rows of data across sheets dynamically

Hi all

I maintain some info that is spread across 4 sheets in a single excel
book .
In the first three I maintain employee info( empid,empname...and other
details reg the employee) of 3 different dept.Empid is the first
column (common)of all the sheets.

Now, my requirement is, when ever I enter an empid in the first column
of the fourth sheet, and, if it already exists in any of the 3 sheets,it should
fetch the details of that employee into the fourth sheet(the complete row). If
the empid is NOT in any of the 3 sheets, then , it should allow me to add details of this employee in the fourth sheet.

Kindly let me know in detail as to how to go about it.

Thank You in advance.

Regards
Eddy

Change sheet names to suit. It is assumed that the employee info starts in
cell A1 of each of the sheets 1 to 3. If not, then change the Cells method
row parameters to suit for all range variables r1 to r3. Example:
Set r1 = ws1.Range(ws1.Cells(1, 1), c) 'Assumed info starts in A1
Set r1 = ws1.Range(ws1.Cells(5, 1), c) 'Assumed info starts in A5

This isn't likely entirely to your needs but it should get you started. This
appears to be your 3rd post on the subject.

Paste the appended code to the code module of the 4th worksheet.

Regards,
Greg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range, r3 As Range
Dim c As Range
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim EmpId As String

If Target.Count > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set c = ws1.Cells(Rows.Count, 1).End(xlUp)
Set r1 = ws1.Range(ws1.Cells(1, 1), c)
Set c = ws2.Cells(Rows.Count, 1).End(xlUp)
Set r2 = ws2.Range(ws2.Cells(1, 1), c)
Set c = ws3.Cells(Rows.Count, 1).End(xlUp)
Set r3 = ws3.Range(ws3.Cells(1, 1), c)
EmpId = Target.Value
Set c = r1.Find(EmpId, LookAt:=xlWhole)
If c Is Nothing Then Set c = r2.Find(EmpId, LookAt:=xlWhole)
If c Is Nothing Then Set c = r3.Find(EmpId, LookAt:=xlWhole)
If c Is Nothing Then Exit Sub
Target.EntireRow.Value = c.EntireRow.Value
End Sub

"knowledgehunter" wrote:

>
> Hi all
>
> I maintain some info that is spread across 4 sheets in a single excel
> book .
> In the first three I maintain employee info( empid,empname...and
> other
> details reg the employee) of 3 different dept.Empid is the first
> column (common)of all the sheets.
>
> Now, my requirement is, when ever I enter an empid in the first column
> of the fourth sheet, and, if it already exists in any of the 3
> sheets,it should
> fetch the details of that employee into the fourth sheet(the complete
> row). If
> the empid is NOT in any of the 3 sheets, then , it should allow me to
> add details of this employee in the fourth sheet.
>
> Kindly let me know in detail as to how to go about it.
>
> Thank You in advance.
>
> Regards
> Eddy
>
>
> --
> knowledgehunter
> ------------------------------------------------------------------------
> knowledgehunter's Profile: http://www.excelforum.com/member.php...o&userid=29333
> View this thread: http://www.excelforum.com/showthread...hreadid=490472
>
>

Hi Greg

That works fine. Thanks a million. Now i need to work around this a bit . Anyway thanks once again for giving me this mighty push.

Regards,
Eddy