Auto-Populate a Sheet Based on Criteria in Another Sheet

[Sirrip]

Hi all,

I have to keep track of people who have over 80% attendance to meetings so that they can qualify for awards.

I have one sheet that has a list of every person (hundreds of them) along with a bunch of data about them (e.g. address, office location, the frequency of their attendance and the percentage of meetings that they have attended etc.).

I want to have a SEPARATE sheet that auto-populates its rows with the name and other data related to each person OVER 80%. If at some point they fall below 80%, I need them to be automatically removed (and vice-versa).

Is this something for VBA? What is the best way to approach this problem?

Any help would be very appreciated.

[rosilisk]

If you could post your spreadsheet, even with the data removed so you wouldn't be posting information, we could probably build the code for you.

This sounds like something that would be best done using VBA.

[Sirrip]

Here's a very simple example, since I can't send the real file.
Basically, I want the 2nd sheet to automatically COPY all the rows from the first sheet that meet the 80% criteria. Basically it should look identical to if I filtered the first sheet on the percentage column.
I need them to stay in sync, so that if somebody rises above, or dips below the 80% threshold, they either appear, or disappear off of sheet2.

[JBeaucaire]

This little macro will do what you want:

Please Login or Register  to view this content.

1) Right-click the sheet tab and select VIEW CODE
2) Paste the code given above into the VBA module that appears
3) Close the VBEditor
4) Save your workbook, in 2007+ save as .xlsm to enable macros


Now, each time you bring the sheet up onscreen it will update again from the Data sheet.

[Sirrip]

Works great! Thank you sir!!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[KristinaElise]

Hi, I am trying to do something similar but am fairly new to macros. Could you please tell me how I can populate a new sheet with contats who's status is set to "positive"? I've been trying to tweak the above macro but cannot. Here is the basic information of my workbook.

Sheet 1 is called "Master" and has all of the data
Sheet 2 is called "Contacts" and is the sheet I would like to auto fill with "positive contacts"

Columns A-BZ have data in them
Row 1 has a heading (in both sheets)
Rows 2:5010 have data in them (in the Master sheet)
Column BX contains statuses (there are 12 total but only the positive ones will need to be added to the Contacts sheet)

If you could please help me I would be very happy!

[arlu1201]

Kristina,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.