Worksheets with linked and manual data

[dwigginsl]

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

https://docs.google.com/spreadsheets/d/1kNALotKGfcMLiH1adbxQ-_cric1kdjAZ/edit?usp=sharing&ouid=11624...

[#2]

@dwigginsl 

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.

[#3]

Thank you for replying. I didn't initially do that since I don't know if the way I'm linking the worksheet 1 data to the other worksheets will work with any methods recommended.

Here are the basic 3 worksheets. https://docs.google.com/spreadsheets/d/1kNALotKGfcMLiH1adbxQ-_cric1kdjAZ/edit?usp=sharing&ouid=11624.... 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.

[#4]

@dwigginsl 

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.

[#5]

@dwigginsl 

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.

[#6]

@Peter Bartholomew 

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.

[#7]

@mathetes 

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.

[#8]

@Peter Bartholomew 

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,

[#9]

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.

[#10]

As John stated below, the sign-in sheet times and signature are needed for printing purposes only. However, the 3rd worksheet would have data manually entered that would need to remain with that name when it is sorted.

[#11]

@dwigginsl 

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.

[#12]

@dwigginsl 

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.