Creating a list with from a flat file DB sheet

[sorensjp]

I'm asking assistance to create VBA code to extract a few pieces of data to create a list.

The attached file includes a Database sheet from which I hope to pull info from and add it to the "Muster Sheet". The rank, full name and DoD ID numbers for only the Active Duty members listed. I've coded the personnel with a legend posted on the user form AN/AF are for Active Duty members. The DoD ID Numbers include the initials so I don't have to create numbers (I'm using the sponsors number).

I've been searching for some VBA code but cannot find anything close that use (or at least that could understand). I'm still learning VBA and wish I continue. Thank you for your time.

V/r,
Jim

P.S. I must put in a plug for Trevor Easton at onlinepclearning.com his site has helped so much.

[Kenneth Hobson]

I don't see how active duty is listed. I would probably use an autofilter or advancedfilter.

[sorensjp]

I have their ranks listed if not they are dependents. Additionally under the "code" column I use the AN or AF for my active duty personnel. I was hoping to place the info onto the Muster sheet in the order of my mock data listed there as well. I didn't think an advanced filter would do that.

Jim

[southward]

Hi Jim,

I suggest using a Pivot Table to get what you need.

See attached copy of your file modified.

There is are two new tabs (because you have the Muster Sheet protected).

The Pivot table tab is pulling data from the new Sheet 1 which is just a copy of your Database sheet.

Hope this makes sense and is helpful.

Cheers

[sorensjp]

Southward,

I think you are right the Pivot Table works, however I hoped I could then "Trim" the text leaving only numbers for the DoD IDs. Is that possible or am I asking too much? Also sorry I forgot to remove the protection the Password is: Online

Jim

Thank you again for your direction. I used a very simple formula to give me a finished product.

V/r,
Jim

[southward]

Hi Jim,

After much trial and error I think I have come up with a simple solution.
The "Muster Sheet" uses simple formulas to pull data from the "Database" and then filters via the "Code" column. You can hide the column D which is the "Code" that filtered.

Hope this works for you.

Cheers

[southward]

OK, I realized that the previous solution wasn't as good as it needed to be.

Hopefully, this one is.

I created three Dynamic Named Ranges so that your Muster Sheet will always include new information entered on the Database.
The Named Ranges are:
DatabaseCode : for column T
DatabaseFullName : for column E
DatabaseIndex : for columns E to T

The new formulas for columns A, B and C are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The formulas in columns A and B are array formula's. They need to be entered with a CNTL, SHIFT, ENTER.

Let me know if you have any issues with it.

Cheers

[sorensjp]

Southward,

I like your solutions. But I think the Pivot Table solution is best since it is more visual for my users. I've already run with it in it application and distribution. The end users need it as "automated" as possible since most aren't even past very basic Excel users.

Thank you so much for creating solutions for me. They will be helpful in other projects I have in mind little melon (aka: Brain).

V/r,
Jim

[southward]

Glad I could be of some assistance.

Cheers