I have an excel file with 5 sheets. Sheets 1 to 4 have rows of data, sorted under various columns, out of which one column in each sheet is titled "priority" and every row has 'high', 'medium' or 'low' populated in the priority column. I need to extract all 'high' priority items and automatically copy those entire rows into sheet 5 automatically, either real-time, or by pressing an update button or something in sheet 5.

Can anyone tell me how it can be done, either through a macro or a simple 'if' 'then' condition.

Thanks

Well, lets see if we can do it this way then. Whenever the 'hi priority' sheet gets focus, all items with priority 'High' and status NOT 'Closed' will get populated afresh in that sheet (i.e., on getting focus, all items in the sheet will be deleted and a fresh search will populate that sheet). Copies of the items that get populated will remain in their original sheets. The 'hi priority' sheet will only act as a view, all changes, updations, entries etc will be made in the other 4 sheets.

The list could grow, so may have to be dynamic. Can go upto 50 in each tab.

Thanks a ton

What about a pivottable which pulls data from each of the sheet tabs that is setup to only find high priority items?

hi firefytr, thanks for all that info. i am attaching a sample file here. the rows with priority 'high' in the 4 different tabs are also in the HI PRIORITY tab (i forgot one more detail, the status should not be 'closed' for that row). the HI PRIORITY tab is what should be populated automatically and all manual entry should be in the other 4 tabs only.

As per your question as to how the action should trigger, it could probably be one of the two

1. activation of the hi priority sheet should populate the sheet with all the priority 'high' rows, which are not status 'closed'

or

2. place a button or link on the hi priority sheet that will populate the sheet with all the 'high' priority rows from the other tabs, at that point of time.

Let me know which is a better approach and then how to execute that approach

Thanks

Well, the choice is really yours to make. We could also do an action where when an item is marked as HIGH priority and not status CLOSED (i.e. worksheet change event) then that info would be updated to the 5th sheet.

You also need to identify what you want to do about information which is already transferred. Do you want to copy the row no matter what? Or do you want to copy the row and replace the row if it existed previously? Also, what if the item changes from HIGH to a lower priority? Or if the status is changed from CLOSED? Would you also want to delete the original row after it was transferred? And one last question, will the rows in the CSR, CE, PMA & MP sheets grow? None of them go over 20. Will this need to be dynamic for you (grow/shrink)?

Okay. And so the entries currently in 'hi priority' sheet will then be deleted? If not, what about duplicates?