ShadowNinja5844
I was hoping there would be a way to insert and delete a row on the OG sheet and have it reflect on the NEW sheet, type in a new name on OG table and have it populate on New table, and still be able to sort and filter New table independently of OG table.
I think it's that last clause that really renders your goal next to impossible. I am willing to be corrected by others more knowledgeable than I.
The other difficulty is that you're basically using the same sheet (in either case) as both input and output. Essentially, other than the COUNTIF formulas below the table, you're mainly using Excel for its nice rows and columns, the nicely (one might say "militarily") arrayed pieces of information.
It is entirely conceivable that one could create a massive database with the individual accomplishments and requirements discretely entered and stored for each person, using Excel's Power Query capabilities to produce output reports on demand, showing the columns, along with the counts, for whatever viewing audience was needed.
I notice, for example, that the data elements under "HANDGUN" and "RIFLE" are essentially the same. And the data for CBRN has the same plus one. So the raw data collected for the OG Tracker could fall under these headings, with "Req.t" meaning (handgun or rifle or CBRN)
Name, Rank, Req't, M69 Complete, Last Ting, Next Schedule, Qual Status, Expiration
Each person would have three such rows at the raw data stage, but Excel could produce the report arrayed however made sense.
(The Passport data doesn't quite fit that pattern, I know.)
What I don't know is whether a similar "common pattern" is available for the data being captured on the NEW sheet.
The basic idea is that in a re-design, that's the kind of thing one would look for in separating data collection from reporting, and doing the data collection in the most efficient, least redundant way. I think the technical term in database design is "data normalization." What you're doing is combining input and output all in one sheet, which involves more intensive "manual" work. A better design would just collect the data and then use Excel to do the summary combining, sorting, sifting and reporting, slicing and dicing with all of its marvelous functions.
As it is, I would keep it all as one sheet--it looks like that's very much what you're doing already--and just report with the OG Tracker sections to leadership. (Are the reports printed, or do they see the actual Excel? If they're seeing the actual Excel, then your concern about overwhelming is entirely understandable)
