Forum Discussion
Worksheets with linked and manual data
You would help us help you by posting a copy of the three sheets you've already been working with. Otherwise you're in effect asking us to create from scratch, which is possible, but .... well, it would just be a lot easier to see and work with what you already have.
Keep the names anonymous as you've done in your quick example. Post the workbook(s) on OneDrive or GoogleDrive and paste a link here that grants access.
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.
Offhand I would say that what you want to do is not possible within a functional programming environment such as a spreadsheet because it violates a basic principle of the functional programming. The do what you want, requires the program to be able to access the data as it was in order to write a new version that includes the new name. That implies a change of state which is illegal.
To make such changes, requires user intervention or an imperative programming environment such as VBA which is based on the concept of state change. The best you can do with spreadsheet formulas is to have an unsorted list of manually entered data that includes names as a foreign key which is then used to join separate data tables to form the output. That output can be made to respond correctly to changes you make to the data as input.
I'm curious, Peter: check how I approached it. I didn't do exactly the "how" that was requested, but think I accomplished the "what" and, I hope, without violating some fundamental basic principle of reality.
On the other hand, maybe what I did is covered by your second paragraph.
No VBA, however. Just good 'ol SORT. Well, good new SORT.
Looking up information from the RawData sheet and putting it together by formula, as you have done, is possible and may meet the needs of the OP. What I had in mind was the status of any signatures or manually-entered clock times on the 'SignIn' sheet. Adding 'Carrie' would shift the remaining names and status down a row but the times and signatures would not change so may finish up against the wrong name. I was arguing that any such additional 'raw data' must be static and the name used to lookup the information in order to sort it for presentation elsewhere.
