Automatic updating list of current members from table of all time members

I play a online roleplaying game and keep a list of all members ever in my faction. I have recently acquired a second faction and would like to have a list of current members in the first faction to allow me to select the member whose alternate character is in the second. I do use different workbooks for each faction. I have been able to create the data validation list of all members in the first faction from the first workbook to the second, but i want to have that list only show players whose line shows they are a member. I know that i could always use vlookup to do this but my issue with that is that it leaves blanks in the list and if i make the results a table of its own and sort then the original table sorts as well.

Can anyone help me with this issue?

*I am willing to use VBA if given the correct coding, as I am still learning it.

I don't fully understand what you're getting at.

I think you are saying you have a list of all members yes? And you want to create a list from that in different sheets when they are in a certain group/faction?
If that is what you are getting at then you can creat a list using VLOOKUP showing just members in that faction without blanks but it needs a bit more than just the lookup bit to work.

Can you upload a workbook (replace any sensitive data with false - make sure the format is the same) and show you're expected outcome manually entered. Naturally you don't need tons of data, just a sample will do.

Click Go Advanced and the Manange Uploads to attach a file.

Originally Posted by Harribone

I don't fully understand what you're getting at.

I think you are saying you have a list of all members yes? And you want to create a list from that in different sheets when they are in a certain group/faction?
If that is what you are getting at then you can creat a list using VLOOKUP showing just members in that faction without blanks but it needs a bit more than just the lookup bit to work.

Can you upload a workbook (replace any sensitive data with false - make sure the format is the same) and show you're expected outcome manually entered. Naturally you don't need tons of data, just a sample will do.

Click Go Advanced and the Manange Uploads to attach a file.

I cant upload one filled out with the members as I am waiting to complete it to bring it all live, but I have filled that specific table in with sample information.
The table is on the sheet 'Full Member List' and one of the uses for the list i want to make will be used in the 'Name Of Member' column on the sheet 'Apr 2013', rather than having to sort through or enter the information again.

Thank you for the help on this issue.

So you have a member list which I can see, what do you need to be able to do on Apr 2013 tab? If you can run through an example and I can hopefully provide a solution (if not someone else will jump in to help no doubt).

Does this need to list members whos status is active as a member?

In Apr 2013 sheet, type into C4:

=IFERROR(VLOOKUP($B4,'Full Member List'!$A:$E,2,0),"")

As you have set the table as a table in excel it will populate the rest of the column automatically.

Now in the Full Member List tab select A4 and type:

=IF(D4="Member",MAX($A$3:A3)+1,"")

Drag this down to the end of the table (this will need to be dragged down again if table extends - you could include this as part of the table so it should auto populate for each additional row but it must be the first column).

Now go back to the Apr tab and you will see the members showing that are currently active.

Hope this helps

No probs, that's what we're here for!

Hope your roleplaying goes well, get them D20's on the go!