Excel multiple sheets, linking single column and sorting

Question

I have a workbook with several sheets that contain different tasks on each sheet that is tracked, I have a sheet with the master list of employees that i want to link to each sheet so that it is automatically updated when add or remove a person from the master employee list.

I have tried linking the cells to the master sheet, the problem i encounter with that is that if i sort on sheet the other sheets only sort the column A that is linked and not the rest of the cells also.

Any suggestions or help would be appreciated.

mathetes · Answer

cvervooren&nbsp;&nbsp;Your description is confusing, I'm sorry to say. Is it at all possible for you to post an anonymized version of this (no real names of real people) workbook, so that we could see for ourselves how it's structured? If you can't do it here, you can post such a mock-up on OneDrive or GoogleDrive and post a link here.&nbsp;In the meantime, I'm wondering if you would be open to a bit of re-design? For example, it might well be more effective to track all of the tasks in a single worksheet, which could make it easier for Excel to work some of its "magic" in parsing and sorting, summarizing. On paper it's easier for us as humans to make sense of separate sheets for each task, but Excel actually often works more effectively with a single well-designed database.

cvervooren · Answer

@mathatesThank you for the reply and suggestion.I would be able to track all in one single worksheet if needed or to make it easier to accomplish my need.I am fairly new to working this much with excel, Sorry for the confusing explanation. would it then possible then to pull certain information into another sheet or to filter/sort data by certain tasks or employees?here is the link to one drive document:https://1drv.ms/x/s!Avb-Qw5uBfaXg00-5oW2mUEaVd_B?e=4LW4XVThere are 3 sheets in the workbook, i have limited it for the sake of trying to solve the problem, there is actually 6 tabs altogether.the members tab are all the employees, with position and rank, in the other 2 tabs i have tasks or qualifications that i need for all of these members to be put into.Instead of having to go in and edit each tab with members name and info each time i get or change employees i am trying to make each tab reflect the list of members sheet.

mathetes · Answer

cvervooren&nbsp; Thanks for posting that link to the OneDrive example. I'm going to take some of your paragraphs and answer the questions you've asked, and also ask a few more&nbsp;I am fairly new to working this much with excel, Sorry for the confusing explanation. would it then possible then to pull certain information into another sheet or to filter/sort data by certain tasks or employees?For somebody "fairly new to working this much with Excel" you are tackling a BIG project. So I'm glad you're open to suggestions.&nbsp;It definitely is possible to have a LOT of information in one (or more) tables of data and then summarize or analyze it, filtering and/or sorting in another sheet. If you google (or look on YouTube) for topics connecting the terms "Dashboard" and "Excel" you'll see examples. There's also this YouTube video (somewhat advanced) describing a very powerful and relatively new FILTER function, along with several others like SORT and UNIQUE. These might come in handy for a dashboard, if that turns out to be a useful concept for you.&nbsp;the members tab are all the employees, with position and rank, in the other 2 tabs i have tasks or qualifications that i need for all of these members to be put into.&nbsp;Instead of having to go in and edit each tab with members name and info each time i get or change employees i am trying to make each tab reflect the list of members sheet.&nbsp;Please describe a bit more fully what the full functions and procedures are that you expect here? Maybe thinking in terms of Inputs -- what &nbsp;data will you be entering, where -- and Outputs -- what are the results or reports or outcomes that this is to serve?&nbsp;There are 3 sheets in the workbook, i have limited it for the sake of trying to solve the problem, there is actually 6 tabs altogether.&nbsp;What are the other three? How do they relate in terms of the Inputs and Outputs that my previous question asked about?&nbsp;Finally, if you have any actual data to enter into any of these--representative of what will be being recorded--that would be helpful. Right now it's essentially a blank sheet, so although it conveys some idea of what is involved, it's kind of like the skeleton and not the whole. Again, this is where a more complete description of Inputs and Outputs will be very helpful in helping me (and others) help you.

cvervooren · Answer

mathetes&nbsp;&nbsp;thank you for the links and info, i will see what i can get out of those.&nbsp;&nbsp;Please describe a bit more fully what the full functions and procedures are that you expect here? Maybe thinking in terms of&nbsp;Inputs&nbsp;-- what &nbsp;data will you be entering, where -- and&nbsp;Outputs&nbsp;-- what are the results or reports or outcomes that this is to serve?&nbsp;essentially there are about 100 tasks altogether, basically broken down into 4 types of certifications. i had each tab setup as a different certification with those tasks listed.&nbsp;&nbsp;each task input was either a "calendar date" or an input of "completed, not completed, in-progress" depending on the task type.&nbsp; &nbsp;The tasks with calendar date inputs are setup to expire after a certain amount of time passes, i have it indicated to change the cell color after it indicates it is expired. such as a reoccurring tasks.&nbsp;The output would be to sort those tasks/employees by different certification levels, or the different types of inputs.&nbsp;each tab i had setup (or for each certification) had all the same type of input information, it was either all date inputs, or other type of inputs it was not blended.&nbsp;the other tabs i mentioned where just more of the same type as the first three, included others tasks for different certification levels.&nbsp;

Forum Discussion

Excel multiple sheets, linking single column and sorting

Share

Resources