Updating excel sheet with selected data from another sheet in the same file

[gsnivas]

Hi,

I have a excel sheet, sheet1 having lot of information as shown below:

Alloc. Status----------Emp. Name
(in Column A)---------(in Column B)
_________________________

Domestic---------------Emp1
Overseas--------------Emp2
Internal----------------Emp3
Leave-------------------Emp4
Overseas---------------Emp5
Internal-----------------Emp6

I would like to update another sheet, sheet2 in the same file based on the alloc.Status, eg.Domestic.

Sheet2 should get updated automatically whenever there is a change in sheet1.

I found some formula at the URL below:

http://office.microsoft.com/en-us/as...260381033.aspx

But that formula is working only in the same sheet. It's not working when I used it in Sheet2.

Thanks in advance
GSNIVAS

[gsnivas]

Hi all,

Following is the formula which is working:

=IF(ISERROR(INDEX(AllIDM!$A$1:AllIDM!$C$500,SMALL(IF(AllIDM!$A$1:AllIDM!$A$500="Branch",ROW(AllIDM!$A$1:AllIDM!$A$500)),ROW(Sheet3!1:1)),3)),"",INDEX(AllIDM!$A$1:AllIDM!$C$500,SMALL(IF(AllIDM!$A$1:AllIDM!$A$500="Branch",ROW(AllIDM!$A$1:AllIDM!$A$500)),ROW(Sheet3!1:1)),3))

AllIDM is the main worksheet having complete data, Branch is test condition.

Thanks
GSNIVAS