How to indicate tell Excel that if someone has accomplished all training on certain training, mark an "X" in a box?

[jbirwin20]

I'm a special needs Excel user so that Title is a good as I could get for a description. But I added photos to help. Basically, my job requires me to track training for pilots. However, what I would like to do is to have two spreadsheets talk to each other.

The first image is a picture of what's called a Redball Tracker. It allows us to check off training items and make sure all the pilots are good to go before they fly. In the pilot and wso columns I added drop-down menus for all the aircrew members. The second image is the training tracker we use that's updated daily. To sum up what I'm trying to do, whenever I choose an aircrew member, is there any way that when the pilot/wso cell populates with an aircrew member, it can then see if the member is overdue on any training. If the member isn't over due then add an "X" in the "GNG" cell next to it? I realize this is convoluted but this one is stumping me.

https://imgur.com/cTS2itA

https://imgur.com/xYmjlBy

 

[jbirwin20]

After compartmentalizing the formula you wrote out it's not as hard as it looks. I still need to do more research on some of the little things but other than that I feel like it's making more sense. Would you be opposed to me ever emailing you this spreadsheet to help me add on to it? I'm the only one in the office that seems to be willing to use Excel to make out lives easier. Everyone else is just in that "this is the way we've always done it" mentality and I'm trying to mitigate that. Plus it's frustrating when you see leadership look at us like we're incompetant if we're not providing data in a promptly manner. If you're willing I'd like it if you could help walk me through another project. I'd even PayPal you some money if that's what it takes sir. The way I see it, if I could provide a really good Excel product for my commander and it gets me recognition and I see it as being worth it.

 

[jbirwin20]

I'm not seeing my last message in the thread. These message boards act wonky. But I just wanted to say thanks again for the lesson and this is going to make me look really good to my leadership. One laaaaaast request if you don't mind helping sir. I showed the spreadsheet to my supervisor and she was wondering if I could include columns C and D. Column C is the crew members aviation service code, and column D is their physical availability status. Normally, these don't change very often but it is a crucial thing to be aware of in case they do change. Would there be another condition that we could add onto the original that says: "If column C contains 1X, make the crew member a NO GO!". And for the availability status column: "If column D contains a D or G, make the crew member a NO GO!" This should be the no-kidding, last inclusion for the redball tracker. After that I'll leave you alone lol.

https://imgur.com/S1GXZyE

I tried breaking the formula you made down to see how I would word it. I would assume it wouldn't be as complicated for it to flag simple texts like 1X (column C) and D, and G (column D).

=IF(AND(COUNTIF(INDEX('Go-No-Go'!$E$5:$O$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0),"<"&TODAY())=0,INDEX('Go-No-Go'!$P$6:$P$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0)>EOMONTH(TODAY(),-1),COUNT(INDEX('Go-No-Go'!$Q$6:$S$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0))=3),"X","NO GO!")

 

[Eric W]

I'm sorry I haven't replied sooner, I've had a crazy week. Based on the formula you posted in the last post, and based on the picture you posted, I'd probably adapt the formula as follows:

=IF(AND(COUNTIF(INDEX('Go-No-Go'!$E$5:$O$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0),"<"&TODAY())=0,INDEX('Go-No-Go'!$P$6:$P$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0)>EOMONTH(TODAY(),-1),COUNT(INDEX('Go-No-Go'!$Q$6:$S$78,MATCH(D5,'Go-No-Go'!$A$6:$A$78,0),0))=3,COUNTIFS('Go-No-Go'!$A$6:$A$78,D5,'Go-No-Go'!$C$6:$C$78,"1A")=0,SUM(COUNTIFS('Go-No-Go'!$A$6:$A$78,D5,'Go-No-Go'!$D$6:$D$78,{"D","G"}))=0),"X","NO GO!")

I added 2 more conditions to the AND. First, the count on the Go-No-Go sheet for the flier name and "1A" must be 0, and the sum of the counts for the flier's name and G or D must be 0. If not, the whole AND will be false, and the "NO GO!" will display.

If I'm not around, send me a PM. This will send me an email to my home account, so I'll know to come look for it. Good luck!

 

[jbirwin20]

Hey Eric it's me again. Was wondering if you could help me again. Is there anyway I could display new crew members on the go/no-go spreadsheet that need to be added. So the output file contains all the newest/raw data from our database. So when a new crew member is added it'll show up on the output file but won't display on the go/no-go until we manually add them in. But it would be much easier if I could just display in the top right corner a "New Crewmembers!" box and have the listing below so we know we need to add than. I'm assuming it would be an IF/THEN statement. Like if column A of the go/no-go doesn't contain the names in column A of the output file then display the names (without all the duplicates) in column (any unused column next to the data) in the go/no-go spreadsheet. I'm sure this is possible. Any way you could guide me in the right direction Eric?

 

[Eric W]

If your raw data book!Sheet1 looks like this:

Excel 2012

	A
Crewmembers
John Doe
Al Capp
Jack Dalton
Mark Twain
Ed Ames
Dan the Man

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet1​

and your training sheet looks like this:

Excel 2012

	A	B	C	D
Name	New Crewmembers
John Doe	Mark Twain
Al Capp	Dan the Man
Jack Dalton
Ed Ames

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX([Book1.xlsx]Sheet1!$A:$A,SMALL(IF([Book1.xlsx]Sheet1!$A$2:$A$15<>"",IF(ISERROR(MATCH([Book1.xlsx]Sheet1!$A$2:$A$15,$A$2:$A$10,0)),ROW([Book1.xlsx]Sheet1!$A$2:$A$15))),ROWS($D$2:$D2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



You can use the formula in D2. Change the references to match your books, then press Control+Shift+Enter instead of just enter in the formula bar. (It's easiest to enter the formula while both books are open, otherwise you need to put in the full path, and that's a pain. After you enter the formula, you can close Book1, and you can see the entire delimited formula.) Then you can copy it down the columns as needed.

Good luck!

 

[jbirwin20]

Hey Eric,

Sorry for the delayed response, I was TDY to Florida and got busy. But just so I'm tracking this formula will work in the same spreadsheet? I got confused with your "enter the formula while both books are open, because I have the Output File in the same spreadsheet. Also with the "[Book1.xlsx]Sheet1" am I putting "[Output File.xlsx]GO-NO-GO"? If that's the case when you put "$A:$A" afterwards, that's highlighting the entire row of names in the Output File, and "$A$2:$A$15<>" is highlighting the row of names in the "Go-No-Go" sheet. The IF(ISERROR(MATCH portion is saying that if they're any names in the Output File that aren't in the Go-No-Go sheet then those names will be displayed in column D, due to the $D$2:$D2 portion? Apologies you have to break it all down for me like this lol.


=IFERROR(INDEX([Book1.xlsx]Sheet1!$A:$A,SMALL(IF([Book1.xlsx]Sheet1!$A$2:$A$15<>"",IF(ISERROR(MATCH([Book1.xlsx]Sheet1!$A$2:$A$15,$A$2:$A$10,0)),ROW([Book1.xlsx]Sheet1!$A$2:$A$15))),ROWS($D$2:$D2))),"")


https://imgur.com/DjK0dh2

https://imgur.com/aVFlQOS

 

[Eric W]

Yes, of course you can use this in the same workbook. I only suggested that you "enter the formula while both books are open" because IF the formula refers to another workbook, it's easier to enter the remote cell references with only the workbook name. If the remote workbook is closed, you also need to include the full path name, which is a pain. If you have the remote workbook open, add the formula, then close the remote workbook, Excel will add the path name for you. If you include both sheets in the same workbook, you don't need the workbook name, just the sheet names.

It's been a while since I've looked at your workbooks, so it's hard to remember what's going on. The formula you posted in your last post appears to create a list of names that appear on Sheet1!$A$2:$A$15, but don't appear on the sheet with the formula on it. It doesn't appear that it would work on either of the sheets you created an image of. You'd have it on another sheet?

Yes, I use the entire $A:$A column reference since the SMALL part of the formula returns a row number, and the INDEX gets the proper item. No need to specify rows, it's cleaner/more efficient this way. I use the $A$2:$A$15 references to limit the number of rows under consideration. Change the max row number to something below the bottom of your data. A whole column reference here would be terribly slow. The ROWS($D$2:$D2) section returns the 1st, 2nd, 3rd, etc. instance of a name we want to display. Yes, I usually use the column reference (D) where I put the formula, although it's not required to do so.

 

[jbirwin20]

Eric that did it. You would not believe how helpful this is for me. I did have a question about something else entirely tho. Not really formula related. Do you know if there's a way Excel can notify you if I hyperlinked folder has been updated? Our job requires us to update our database with these forms that the training office puts into a folder on the drive. The only problem is it doesn't happen very often so it can get forgotten about. I was thinking if I can hyperlink the folder on our day to day checklist, we could have it notify us if anything has been input. Is that even possible?

 

[Eric W]

Glad your got your training book squared away.

As far as the hyperlinked folder, I can't think of anything immediately that would be able to tell if something had been placed in it. The best I could come up with is to write a macro that checks the contents, and if something is new, it would tell you. I don't know what your daily routine is, but if you have an Excel file you open daily (or weekly if you don't need to check too often), we could add the macro to that, and either have it automatically run when the workbook is opened, or add a button to run it.

But first I'd recommend you open a new thread with just the hyperlinked folder question. There are a lot of smart people out there who might have a better idea. I'll watch for it.

 

[Eric W]

@jbirwin20 ,

I hope you get this! I tried to send you a PM, but your inbox is full. You'll need to either clean out some messages, or open a thread I can reply to, or send me your regular email address via PM and I can reply to that.