Conditional reference of an enitre row from other worksheets

basavel

New Member
Joined
Jul 23, 2007
Messages
0
Hello Excel geniuses :)

I use excel to track the task delegated to my subordinates. I have a separate tab(worksheet) for each employee. The tabs have common format -one of the columns beings status (open, in progress, finished).


What I'd like to do is to be able to track on a separate master sheet all tasks (and responsible people) that have their status as open or in progress (or not equal to finished). So what I need is something that would scan all the worksheets and if a row has value in "status" column not equal to "finished" display the entire row in a separate worksheet ("unfinished tasks") so that I could easily see who should I chase. I know it might be simpler to put everything into one worksheet and use filters but I'd like to have it in this format. Also there were some instances when I needed that kind of approach in the past and I did not know how to do it so I'd like to learn.

I hope you can help me

Thanks in advance
Bas
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Assuming status is in col F , paste the following codes in the macro window ( Alt F11)

Code:
Sub unfinished()
Sheets.Add.Name = "unfinished"
c = 1
For a = 2 To Sheets.Count
x = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
For b = 1 To x
 If Worksheets(a).Cells(b, 6) <> "finished" Then
 worskheets(a).Rows(b).Copy
 Worksheets("unfinished").Rows(c).PasteSpecial
 c = c + 1
 End If
 Next b
 Next a
End Sub
change cells(b,6) to cells(b,col no of status) if it is not F.
Run the macro. It will insert a sheet called unfinished and lists unfinished jobs from all sheets
Ravi
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,523
Members
425,479
Latest member
Neerajcool

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
Top