Forum Discussion
Replicate and edit 2 different tables
I have a Workbook that tracks some qualification data for the personnel in my office. I have been asked to track even more training requirements. Instead of over crowding my original tracker (OG), I want to make a new table (NEW) on a separate sheet to track these new items.
<snip></snip>
I realize now, I have not gone about this the right way.
I recommend reflecting a bit more on that last sentence.
Your description--and granted, you didn't give us all the details--makes it sound like you may indeed have not gone about this in the right way. If it's a dynamic population, as it appears, with people being added, probably some leaving, moving, new requirements being added, some removed......it just would make more sense to keep it all as a single file, single database.
Perhaps organized differently than it is--you don't tell us how it's organized, whether you keep all data by as requirements/qualifications per individual, or per job/position category, how many such requirements or qualifications there are per position/person.
But almost by definition, trying to maintain two separate systems with redundant/overlapping purposes is asking for trouble. You're just beginning to experience some of those difficulties.
Is it possible for you to share a sample of how your (two) sheets are organized, removing all actual names and any other data that would violate confidentiality. Post a copy on OneDrive or GoogleDrive with a link pasted here granting access.
mathetes sorry I know my description was terrible I tried uploading the Worksheet but wasn't sure how so thank you for the direction.
We use this to track the training for a couple hundred individuals. The OG table we work with on a near daily bases and report to leadership on. The NEW table is a new tracker that we are looking to use, due to process/website changes. I did not want to overload Leadership using the OG table with the information from our new tracker.
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.
Here is a link to the doc on Google Drive:
https://docs.google.com/spreadsheets/d/1t_H_cx-MJYCpQ2WBjW3s4QqZbI_sWOoH/edit?usp=drivesdk&ouid=110887371485894765647&rtpof=true&sd=true
Thank you for any help you can provide.
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)