Ok this is kind of complicated but I will do my best to explain what I am having a problem with.
I am working with 4 sheets they are as follows:
NUMBERS (this one is done)
NAMES
ATTACHMENTS (This is not used right now)
CHARLIE ROSTER
What I am trying to do is grab information from the Charlie roster based on set criteria and have it fill in my names roster in the appropriate spot while changing the format. First let me explain what the Columns are in the Charlie roster (this is auto filled in from an access database)
CHARLIE ROSTER COLUMNS
Column A: Status
Column B: Rank
Column C: LN
Column D: FN
Column E: MI
Column F: SSN
Column G: CO
Column H: PLT
Column I: ME/MO/NE
Column J: Status start
Column K: Status end
Column L: Status location
Column M: Limited duty
Column N: Med Non-Deployable
I want excel to look on the sheet CHARLIE ROSTER and grab all the people for example that have the STATUS (column A) equaling BRIG that also have the ME/MO/NE that is equaling ME and have the formula then grab that persons RANK LN, FN MI in some kind of concatenate that will then be placed in the sheet NAMES, this function also has to be able to put that information in multiple lines if there are more than 1 returning result. Here is a snip of the NAMES sheet
A6 = BRIG
A7 = RANK/FULL NAME
B7 = SSN
C7 = CO
D7 = SECTION (this would be PLT)
E7 = Start (This would be Status Start)
F7 = End (This would be status end)
G7 = Location (status location)
Then on line 11 it starts with the same stuff just with A11 being FAP instead, so I need something that will take the results and place the name so that it is something like =CONCATENATE('CHARLIE ROSTER'!B2," ",'CHARLIE ROSTER'!C2,", ",'CHARLIE ROSTER'!D2," ",'CHARLIE ROSTER'!E2) but having it only grab the results I want. I can grab how many I have of one item by doing something like =COUNT(IF(('CHARLIE ROSTER'!A1:A65529="BRIG")*('CHARLIE ROSTER'!I1:I65529="ME"),0)) but I am not sure how to make it so that I can grab all the names and then do a concatenate that would fill in the Rank/full name and all the other required fields. I know this is very confusing and complicated but I am hoping that someone out their understands what I am asking and maybe just maybe has a solution. Thank you for everything, I have tried looking online all over for anything similar but have not been real lucky, if you have any questions about what I am asking please ask. This would save me so much time, and having this automated would save me countless time, and as the CHARLIE ROSTER is getting all the info from a single DB on access this would be a fully automated sheet. Saving countless hours a week. Thank you for any help you may be able to assist with.
LCpl Stewart, Travis M
USMC/0313/Twentynine Palms, CA
(e-mail address removed)
I am working with 4 sheets they are as follows:
NUMBERS (this one is done)
NAMES
ATTACHMENTS (This is not used right now)
CHARLIE ROSTER
What I am trying to do is grab information from the Charlie roster based on set criteria and have it fill in my names roster in the appropriate spot while changing the format. First let me explain what the Columns are in the Charlie roster (this is auto filled in from an access database)
CHARLIE ROSTER COLUMNS
Column A: Status
Column B: Rank
Column C: LN
Column D: FN
Column E: MI
Column F: SSN
Column G: CO
Column H: PLT
Column I: ME/MO/NE
Column J: Status start
Column K: Status end
Column L: Status location
Column M: Limited duty
Column N: Med Non-Deployable
I want excel to look on the sheet CHARLIE ROSTER and grab all the people for example that have the STATUS (column A) equaling BRIG that also have the ME/MO/NE that is equaling ME and have the formula then grab that persons RANK LN, FN MI in some kind of concatenate that will then be placed in the sheet NAMES, this function also has to be able to put that information in multiple lines if there are more than 1 returning result. Here is a snip of the NAMES sheet
A6 = BRIG
A7 = RANK/FULL NAME
B7 = SSN
C7 = CO
D7 = SECTION (this would be PLT)
E7 = Start (This would be Status Start)
F7 = End (This would be status end)
G7 = Location (status location)
Then on line 11 it starts with the same stuff just with A11 being FAP instead, so I need something that will take the results and place the name so that it is something like =CONCATENATE('CHARLIE ROSTER'!B2," ",'CHARLIE ROSTER'!C2,", ",'CHARLIE ROSTER'!D2," ",'CHARLIE ROSTER'!E2) but having it only grab the results I want. I can grab how many I have of one item by doing something like =COUNT(IF(('CHARLIE ROSTER'!A1:A65529="BRIG")*('CHARLIE ROSTER'!I1:I65529="ME"),0)) but I am not sure how to make it so that I can grab all the names and then do a concatenate that would fill in the Rank/full name and all the other required fields. I know this is very confusing and complicated but I am hoping that someone out their understands what I am asking and maybe just maybe has a solution. Thank you for everything, I have tried looking online all over for anything similar but have not been real lucky, if you have any questions about what I am asking please ask. This would save me so much time, and having this automated would save me countless time, and as the CHARLIE ROSTER is getting all the info from a single DB on access this would be a fully automated sheet. Saving countless hours a week. Thank you for any help you may be able to assist with.
LCpl Stewart, Travis M
USMC/0313/Twentynine Palms, CA
(e-mail address removed)