Apr 06 2022 06:41 AM - edited Apr 06 2022 07:37 AM
Apr 06 2022 06:41 AM - edited Apr 06 2022 07:37 AM
Hi, so for my company we have a Project Tracking document that helps us prioritize what we should be working on for the week. In this document, we have a Query as the first sheet for the basic information and then our Priority sheet connected to that with more in depth information because that is where we draw most of the project information from.
So my question is: How do I link different sheets to my Priorities sheet, so that if a project is assigned to an employee on that sheet, it will automatically update on their specific page?
Apr 06 2022 07:19 AM
First of all, let me assure you that there are various ways within Excel to pull data from one sheet to another. Which of the several ways might be most or more appropriate will depend on getting some more details from you, and ideally would involve getting sample copies of your actual workbook.
Second, though, and this is important: your images contain what I must presume are real names of real people. Although you do not include their addresses (email or otherwise), or bank account numbers and balances--i.e., you don't really have super-sensitive data shown--you really should remove those images as soon as possible and replace the real names with, oh, Disney characters or other clearly fictitious names. Same for any other potentially "internal use only" data....anonymity is the rule of this site.
That said, here are some questions:
Apr 06 2022 07:33 AM
Apr 06 2022 07:47 AM
1) Thanks for the helpful information, I just removed the images!
Thanks for doing that so promptly.
3) CK is the person's abbreviated name--So essentially, each employee will have their own "priority page" and that will tell them what to work on each week. There will be a total of about 11 pages, but as our employee numbers grow, so will the sheets in the documents.
And will that data on each employee's page simply be listed in much the same way as on your first or second sheet, just sorted or selected based on priority? i.e., will you be expecting a simple extract of data as is, or are you thinking of a different kind of display? If so, how?
I don't really know how to answer the other questions you asked because I'm still a novice when it comes to excel.
I wasn't asking Excel questions: I was asking what I'd call "business process" questions. Think more what the process would be without Excel: what are the business purposes, the management or supervisory purposes being served. Let those of us with excel experience translate.
Finally, if you could create a "dummy" version of the workbook as it is, could you post it either to Google Drive or OneDrive so that I or others here in the https://techcommunity.microsoft.com site could take a look, that would be VERY helpful.
Just make sure it's representative of the real one, but has Mickey Mouse or Darth Vader or any of their colleagues as the names.......
Apr 06 2022 08:25 AM
Apr 06 2022 08:43 AM
OK, I was able to download that sample file. Thanks!
Here (attached) is one example of a system that could work. This does use a couple of brand new functions--UNIQUE and FILTER--that require the most current version of Excel, so if it doesn't work for you let me know. I've put a few comments in to help you understand what I've done....
I'm off on an errand momentarily, so any further work from me will have to wait till later this afternoon.
Apr 06 2022 08:48 AM
Apr 06 2022 08:58 AM
Apr 06 2022 12:24 PM
My only questions would be:
1) I can technically erase the "Priorities" tab and just have all of my information on one page?
Example: Just have the data validation pull everything from the "Core" tab and I could erase the "Priorities" tab.
Not only can you do so, I'd recommend having only the one source for data.
2) How would I update the range of data validation with each project that I add to where its drawing information from?
Ah, the magic of Excel Tables. In this new one, I have eliminated the Priorities tab, so it's drawing from the Core. And I've also eliminated all the empty rows from Core. Now, here's the magic: add a new row, with a new manager, and see what happens to the data validation list. I've already added two new names to demonstrate.
Unless I'm mistaken, you should be able to copy your entire original database into Core and see the list magically updated to include all of the unique names......and the sheet with Filter will work with any of them.
Apr 06 2022 01:04 PM
Apr 06 2022 01:48 PM
Apr 06 2022 01:56 PM
@mathetes Yes, when I tried to add the new sections, they came up as "0" on the responding sheet
Apr 06 2022 02:19 PM
You need to modify the formula in Cell A7 of the Selected Priority sheet, the very first argument there...basically by "re-selecting the entire table from Core (not the heading row, but ALL the rest).
=FILTER(Core_2,Core_2[Manager]='Selected Priority'!B2,"None found")
The part I've highlighted in bold above. Highlight it in cell A7, then select all of the table in Core. And hit enter. The formula will still read the same, but it should work.
Your sample had included only those limited columns. If you can't get it to work, see if you can now attach a file to the bottom of your message, or, if not possible, go back to sharepoint and let me know.