Forum Discussion
Worksheets with linked and manual data
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
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.
- 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=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.
