Automatically copy (and update) specific rows and columns into a seperate worksheet

Hello all,

I have various worksheets where each row is individually identified and contains a specific status, such as ‘ongoing’ or ‘complete’. I need all ‘ongoing’ statuses to be pulled out automatically from all worksheets and be populated into a separate worksheet so that all ongoing details can be listed. However, I only need details from specific columns on each row to be listed (Please see the example attached).

I am not sure whether I need to programme this, use a macro or whether there is another clever way. I would like to have an automatic update, so worksheet ‘ongoing’ should always show ongoing statuses as and when selected, conversely, if the status changes to ‘complete’ then the information is no longer viewed on the ‘ongoing’ worksheet. Is this possible?

The columns will always remain the same, however additional rows will be added when needed.

Many thanks in advance!

Is there a current thread that I can use as a guide? Or is someone able to suggest a method that would be appropriate?

Thanks

Perhaps something like this? Pls see the file attach, using array formula

Regards

Hi azumi,

Apologies for the late reply. This works great, thank you so much for your input - I really appreciate it

I have looked at the formula provided on your attached example and I am now trying to incorporate this into my original workbook. I do however have about 17 worksheets where this formula would apply therefore is there a way to reduce the formula? I don't mind writing out the formula for all my worksheets, as I only need to do this for the once, however is the formula restricted by characteristics in Excel?

Please advise. Many thanks!

Originally Posted by azumi

Perhaps something like this? Pls see the file attach, using array formula

Regards

Yes you more efficient and more speed if you use VBA or macro to do complicated situation, I am zero VBA, but maybe other member in this http://www.excelforum.com/excel-programming-vba-macros/ could help you.
Just post new thread hope you lucky to get help in there....

Cheers

Hi Richard,

Thanks for your reply. The data is kept on seperate sheets as the data is split into categories which contain a lot of other information. It would not be workable to have all the information in one worksheet due to the level of information.

Regarding your sugesstion to add an axtra column on the data, are you able to explain / provide an example on how that would work please.

Many thanks.

Originally Posted by Richard Buttrey

Can I first ask why you are keeping data on separate sheets? Why don't you keep all information on a single database sheet?

If the sheet tab names have some significance then create an extra column on the database. If you arrange your data thus your problem will disappear since either filtering your data will allow you to summarise it or better still a Pivot Table would give you total flexibility.

Hi Richard,

Thanks for your input again. I understand where you coming from in regards to placing all data on one worksheet so that it is easier to view, however I dont think this would be user friendly based on the purpose of workbook, i.e. there will be many users who will provide information through this workbook and there is a lot of information on each category / worksheet. Your solution works great for what I am trying to achieve however the pivot table will only look up data on one worksheet. Also, correct me if I am wrong, but the pivot table does not automtically update / refresh the results (not that it is much of a concern but if this is possible then it would be great) - Just wondering whether there is a way to pull all the data from the required columns (A,B,C,T) which only contain an 'Ongoing' status in columnT ignoring any blanks, into a seperate worksheet so that all the required data is copied into a single worksheet, and from there apply the pivot table, if possible.

Thanks for looking into this!

Originally Posted by Richard Buttrey

Hi,

I have to question your assumption that you NEED separate sheets just because there is a 'lot of information'. If you think about it the information has to be held somewhere. You've chosen to distribute it across many sheets but there's generally no over-riding reason why it can't all be on one sheet. You're holding exactly the same amount of information, just in a different layout. You also avoid all the white space that you get with individual sheets since you're records are in a nice compact layout.

But the real big advantage is that it is so much easier to analyse. I've added a typical Pivot Table in the attached and put your original data into a new sheet called 'Data'. All your original fields are there. You don't say what all the text columns mean so I've just given them a generic name 'Label ' Label 2' etc which you can select from a drop down. The additiona filed is the one called 'Origina sheet' for want of a better description. Here it identofies the sheet name the data came from since I'm assuming the sheet names are meaningful and relevant although that isn't obvious from your required results.

In the Pivot table you can see a drop down at the top that will, if you want allow you to filter the PT by the original sheet name. Although you've not asked for any statistical analysis I've just added a simple count of the causes to give you an idea of the flexibility. Just drag that field name out of the Values area in the PT field list pane.

Hi azumi,

I am now incorporating the formula that you had provided to my actual workbook, which contains 18 worksheets. I have started to include the first worksheet in the formula but I dont think I have done this correctly - please could you have a look at the attached.

Also I do not quiet understand what this part of the formula is doing: ROW(B1)-COUNTIF('Area 1'!$T$3:$T$19,$A$1))
So, if I click on cell B3, why does the formula refer to cell B1?

Thanks in advance!

Originally Posted by azumi

Yes you more efficient and more speed if you use VBA or macro to do complicated situation, I am zero VBA, but maybe other member in this http://www.excelforum.com/excel-programming-vba-macros/ could help you.
Just post new thread hope you lucky to get help in there....

Cheers