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

 

[Eric W]

Welcome to the Board!

My son's a WSO, so anything you can do to keep them safe is much appreciated!

Try this formula in the GNG cell of your Redball tracker:

=IF(COUNTIF(INDEX('[C:\Folder\training tracker.xlsx]Training'!$E$5:$S$20,MATCH(E5,'[C:\Folder\training tracker.xlsx]Training'!$A$5:$A$20,0),0),"<"&TODAY())=0,"X","At least 1 out of date training")

Put the name of your training tracker spreadsheet in the places marked in red, and the tab name in green. I assume that the cell with the pilot/wso name is E5. I assume the names in the training tracker are in A5:A20, and the dates are in the corresponding rows of E5:S20, change those ranges as needed.

This basically shows you how to do a COUNTIFS on another workbook. Let me know if you have any questions.

 

[jbirwin20]

And sorry I should've screenshotted with columns were where lol. Here's a snapshot the redball tracker again.

https://imgur.com/KMgwZB7

 

[Eric W]

Based on that picture, the only change needed is to change the E5 right after the MATCH to D5. I can't tell if any changes are needed because of the layout of the training tracker.

 

[jbirwin20]

I'm sorry Eric I thought I responded already but I guess it didn't go through. Once again thank you so much for this. However, if you'd be willing, could you help me out with two more formulas for this? The column that shows SEPT (GS03) is a monthly requirement and is dated whenever they accomplished. So the formula you originally sent reads it as if it's overdue. Additionally there are three one-time events that pilots and wso's need to accomplish before flying (LL01, SS01, and XVID). I moved the SEPT all way to the end of the recurring events and the one time only events after the SEPT. Hopefully making it easier to add more formulas. so my question is, is there a formula we could add on to the original one that says, whenever the SEPT column shows a date before the beginning of the month, make them a no-go. Additionally, for the one-time only events, if those three columns have any blank spaces, make them a no-go. I hate to keep asking you for help but you're the only one that has been able to give me answers.

 

[Eric W]

Sorry I haven't replied sooner, I've been busy, and this particular problem takes a while to set up and test. The revised formula is:

=IF(AND(COUNTIF(INDEX('[Training tracker.xlsx]Training'!$E$5:$O$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0),"<"&TODAY())=0,INDEX('[Training tracker.xlsx]Training'!$P$5:$P$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0)>EOMONTH(TODAY(),-1),COUNT(INDEX('[Training tracker.xlsx]Training'!$Q$5:$S$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0))=3),"X","At least 1 out of date training")

This looks up the right row on the training spreadsheet, and requires that the values in E:O are all future dated, the value in P must be equal or after the first of the current month, and that the cells in Q:S are all dates. You'll have to enter this formula when the training spreadsheet is open, but you can close it after that, and the formula will read the closed workbook.

 

[jbirwin20]

Thank you Eric, I'll test it out. Btw if you don't mind me asking what ats is your son a Weapons Systems Officer? Our WSOs fly in F15s.

 

[Eric W]

He flies B-1's in Ellsworth, so a very different plane.

 

[jbirwin20]

That's really awesome. Saw one at an airshow in Barksdale AFB. Well Eric before I leave you, would you care to give me a few points of reference of understanding this formula? There's so much to the structure it's pretty overwhelming and I'd like to educate myself on it and become an Excel guru like you. For example as I was looking at your formula and asking myself: "What the hell do the commas do?", "Why is there a "!" at the end of the spreadsheet?", and "Why are there so many (()()()()((()())((?????" lol. I'd like to be more self-reliant in case generous people like yourself aren't around.

 

[Eric W]

In principle, the formula is pretty simple, you just need to break it down into parts. It starts like this:

=IF(AND(condition1,condition2,condition3),"X","At least 1 out of date training")

where condition1 is all values in E:O must be future dated, condition2 is P must be after the first of the month, and condition3 is Q:S are dates (non-empty cells). The AND requires all conditions to be true to get an overall true value, which would return the X.

Condition1 starts out as

COUNTIF(range1,"<"&TODAY())

Again, pretty simple. It just counts all the cells in range1 that have a date less than today. TODAY() requires the () even though there are no parameters, just for consistency with other functions. Now here's where it starts to look complicated. range1 is in another workbook. In order to refer to a range in another workbook, you need to give the whole address of the workbook, sheet name, and range. That's done like this:

'[Training tracker.xlsx]Training'!$A$5:$A$15

where the spreadsheet is in [], and the spreadsheet and sheet name is in two single quotes and the range on the sheet must follow a ! sign. In this particular formula, it's even worse, since we don't know exactly where the range is. We know the overall range ($E$5:$O$15), but we need to just look at the row corresponding to the pilot name. INDEX can help us out.

INDEX($E$5:$O$15,2,0)

The last 2 parameters here usually tell us how far down and across to go in the given range, so 2,3 would be 2 rows down and 3 columns across, so 2,3 would give us G6. But if you give it a 0 in the columns parameter, it returns all the columns of the initial range, so 2,0 gives us E6:O6. So we use MATCH to give us the row we want, then we use INDEX to give us the specific range within the overall range, then we use COUNTIF to count the dates less than today. The fact that we have to use the remote reference version of the range ('[Training tracker.xlsx]Training'!$A$5:$A$15) instead of just $A$5:$A$15 makes it looks much more complicated than it is.

Incidentally, I had to use the MATCH function 3 times in the formula, once for each condition. If you could use a helper column, you could put the MATCH function in it, then refer to that helper column 3 times in the formula and shorten it a fair amount, and make it more efficient.

Allowing for all the INDEX/MATCH/Remote range manipulations, the second condition is just:

cell>(EOMONTH(TODAY(),-1)

we find the cell from the training workbook, and make sure it's after the end-of-month date from the previous month. EOMONTH does that, given the -1 parameter.

And the third condition just works out to:

COUNT(range)=3

COUNT tells us how many numeric values are in the range, and dates are numbers.


So overall, the process is to organize your sheet in a logical fashion, keeping similar types of data together (which you found out when you rearranged some columns), figure out what rules you want to apply, then figure out what functions handle those rules. A lot of that is just experience, if you didn't know the EOMONTH function existed, you'd have a tougher time figuring out how to apply that rule. The whole formula looks complicated, but if you just concentrate on one piece at a time, it's not so bad.

I hope this helped! Let me know if you have any more questions.