How would I pull data internally through sheets and sort it onto different pages?

Occasional Contributor

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? 

 

 

 

 

12 Replies

@RichJones1998 

 

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:

  • IS all of this, when done, going to be in a single workbook? (If "yes" that will generally make things easier)
  • What does the "CK" mean in the tab "CK Priority"? Are there going to be other "XX Priority" sheets, possibly for different people, different priority levels? How many?
  • What else can you tell us about the big picture here? How many people will be accessing this workbook, for example? Where does the "raw data" come from? How reliable is it? How many data entry points are there within the workbook (this gets at whether all data will reside in a single database, or will there be subsets on different sheets that are actively maintained on those sheets?)
  • Etc
1) Thanks for the helpful information, I just removed the images!
2) Yes this is all going to be in one workbook
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.
4) As far as the information on the big picture, it was all entered manually and as it updated on the first picture, it would update onto the second. The document would be on our company server so everyone would have access to it if needed (14 of us).

I don't really know how to answer the other questions you asked because I'm still a novice when it comes to excel.

@RichJones1998 

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.......

@RichJones1998 

 

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.

Thank you so much! 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.
2) How would I update the range of data validation with each project that I add to where its drawing information from?

@RichJones1998 

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.

Thank you!!! I believe I know how to handle everything else but, I'll ask just in case.

When I tried to add a row to the "Core" sheet, it didn't copy over to the selected priority sheet. How would I go about doing that?
Did you use a new name? And did you select that name then in the Selected Priority sheet? I just tried it and it worked just fine.

@mathetes  Yes, when I tried to add the new sections, they came up as "0" on the responding sheet

RichJones1998_0-1649278532594.png

RichJones1998_1-1649278565247.png

 

@RichJones1998 

 

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.