Dec 13 2023 10:26 AM - edited Dec 14 2023 10:08 AM
I am trying to create an Excel document that will do the following:
Worksheet 1 contains: Name, status (This worksheet will be updated regularly as new people come in)
Worksheet 2 contains: Name and status fed from worksheet 1 AND stagnate fields to be used as a printable sign-in sheet.
Worksheet 3 contains: Name and status fed from worksheet 1 AND manual entry of training information that needs to remain with the name it is originally entered in for.
Issues
1. As Worksheet 1 is updated, I want the other sheets to update, automatically putting the names in alphabetical order. (Adam, Bob, and David are already in there. Carrie is added and automatically sorts between Bob and David without additional action such as run a macro.)
2. As Worksheet 1 is updated, I want the manual information added in worksheet 3 to stay with the appropriate name and status. (When Carrie is automatically placed between Bob and David, the data that has been entered for David, stays with David's line rather than becoming Carrie's data.)
I've tried a few different methods to achieve this without any luck. Any suggestions would be appreciated.
Thanks
Dec 14 2023 08:03 AM
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.
Dec 14 2023 10:08 AM
Dec 14 2023 12:08 PM
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.
Dec 14 2023 01:02 PM
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.
Dec 14 2023 01:06 PM
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.
Dec 14 2023 01:42 PM
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.
Dec 14 2023 01:50 PM
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.
Got it. I was (and AM) assuming that the sign-in sheet was to be a hard copy printed output, printed using whatever names were live and active at the time. Because of course, if it's used instead as an on-line sign in while, simultaneously new names can be added and sorted into that static list......
So we'll have to wait and see what @dwigginsl has to say about how the sheet(s) are to be used,
Dec 14 2023 01:51 PM
Dec 14 2023 02:01 PM
Dec 14 2023 02:04 PM
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.
Dec 14 2023 02:17 PM
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.