Forum Discussion
Worksheets with linked and manual data
Here are the basic 3 worksheets. https://docs.google.com/spreadsheets/d/1kNALotKGfcMLiH1adbxQ-_cric1kdjAZ/edit?usp=sharing&ouid=116248979396268948587&rtpof=true&sd=true. I'm looking for a method to add a name to the first sheet and it automatically alphabetically insert that name in the other two sheets without moving any manually entered information associated with the name in those sheets. Let me know if there is anything else I can provide to help with this.
Thanks again.
OK, I think this will work. I had to change your fundamental idea just a bit. What you had as Sheet 1 is now the repository of "RawData." Sheet 2 becomes "SignIn" --and does what you want. Sheet 3 is solely now an output sheet, because you enter the training data in "RawData" associated with the right name. In "TrainingOutput" those names are sorted and the correct training data shows up next to them.
To do this, I use the dynamic array function SORT in column A of the second and third sheets, and then a series of INDEX and MATCH combinations to retrieve the information. For SORT to work, you'll need Excel 2021 or newer (or Microsoft 365)
I've only entered the formulas down to row 25 of the two output sheets, and put borders on those to highlight that. They can be copied down as far as desired.
- John,
I appreciate your work on this. Your method was going to be my fall back if I can't figure out a work around. The only issue is the example I gave you was a simplistic version or what needs to be done. The 3rd worksheet in my example (the one with manual entry of data), it is only 1 of 6 similar worksheets in the actual document. Each worksheet has at least 15 columns of data. If I use one main entry worksheet and all the rest are reporting worksheets, it will be enormous.
I am making this for someone who has had trouble in the past with maintaining the document without having formula mishaps. I'm trying to make it as simple as possible without having one worksheet be overwhelming. The only way I can think to make this work is to create a macro that will run all sorting after new names are entered in the first worksheet (which will maintain all names and status). And I'm not even sure that will work.
Oh, and thank you for the Sort idea. I haven't worked with that yet. It will come in handy on other projects.I would still go toward a single raw data sheet, just organized differently. Especially if all you're storing for any given training are dates, it would be easy to create records that consist of
Name, Status, Course Designation, Date
And to make it more efficient, use ID rather than name and status, and have another table that converts ID into name and status.
I speak as someone who managed an HR/Payroll database for a major corporation.....you can normalize the data around training in someway, in the database, and let Excel slice and dice for output.
The categories of raw data can be spread across many worksheets but the names must be hardcoded on each and not generated by formula. The presentation sheet can then look up data from each sheet using the name as the link. That formula used to generate that sheet can filter or sort the order of information as presented to the user.
