Hi everyone....
So I've searched Google as well as I possibly could without knowing all of the search criteria which may be helpful, but since I made a post about this on Reddit that details the goal that I'm after, I'll simply paste it over to here and go from there. It's uncommon to encounter a lack of responses over there, so I'm left to assume one of two conclusions. Either 1.) I'm unnecessarily complicating things to an extent which can easily be avoided due to a lack of experience with this, or 2.) I'm too much of a beginner to be helped. I'd like to think that it's the former, although neither of those potential causes are anything I'd care to resign myself to
At any rate, here are the details of what I'm trying to accomplish. Please note that I can offer clarification as needed and visual references upon request, if that should help answer the question. Ideally, I'd avoid the use of a macro and instead use a formula. That way, there can be no excuse to not have the table updated in the final spreadsheet that I'm looking to have populated using my own "parent" sheet which I update regularly. In case it wasn't implied from that statement, I'd be using this to delegate tasks to different departments without having to hold a meeting to do it, unless absolutely necessary for critically important matters. However, if the use of a macro is the only way to do this, then so be it.
Link to the original post (the post itself pasted below this, so you can use whichever medium you prefer): https://www.reddit.com/r/excel/comments/84ef4r/table_relationships_question/
Content pasted from the original post:
Note: I've been updating this post with my progress made, which you can find at the bottom. Currently, I've almost solved this, but need assistance on "skipping" empty entries and moving on to the next row.
Before I get into this, I'd like to thank you guys for all your help thus far. I've been attempting to streamline much of the project communication within my department (doing so using Excel), and you've all been a great help with everything I've tried to do. I've added a ton of functionality to one simple spreadsheet and it's becoming a centralized source of information for multiple outlets, which is super helpful because having multiple sources of information leads to more problems than it's worth, at least to my experience. Which leads me to my next request..... having department-specific spreadsheets which are populated by a formula which pulls information from a parent spreadsheet/table which I will be updating as needed.
Basically, my goal is this: I have a "Task Log" which is a table that I update after meetings with my manager, which is broken down by "Priority Level" and "Department" (amongst other things, but these are the important columns for the purpose of this request). The prioritization system built into it is a combination of "priority" and "sequence number", which basically translates out like "1.1" for "priority 1, first in line", then "1.3" would mean "priority 1, third in line" or "3.2" for "priority 3, second in line". It's fairly important that I clearly explain how this system works, so to summarize this, see the associations below:
Priority Level/VLOOKUP Associations:
Note: I have both numbers as well as alphabetical designations for priority levels; 1, 2, and 3 are (perhaps obviously), priorities 1, 2, and 3, respectively. The alphabetical designations on the other hand, are less intuitive. R = Routine, H = Hold, and C = Complete. Since the sorting default that Excel uses wants to go from 1 to 3, then from A to Z, I had to apply a custom sort to ensure that all the "C" designations got kicked to the bottom instead of being in the middle somewhere. It didn't seem to work when using "1 to 3.99, Z to A" as a custom list, so I had to associate each priority level with numbers using vlookup, and custom sort by those numbers. This is why you see the order below.
1 to 1.99 = 1
2 to 2.99 = 2
3 to 3.99 = 3
R to R99 = 6
H to H99 = 5
C to C99 = 4
Custom Sorting Structure:
Sort by: VLOOKUP > Values > "1, 2, 3, 6, 5, 4"
Then by: P > Values > "1 to 3.99, Z to A"
As a side note, I have no idea why "1 to 3.99, Z to A" on the "P" column worked as a secondary sort and not a primary sort. Sort of frustrating, actually...
At any rate, that's the priority level hierarchy which I use for task management, and it works out fairly nicely. I use it for two tables which are side by side, one for "Tasks" which are primarily 'one and done' tasks, and the other for "Projects" which are generally 'ongoing' tasks. What I'd like to do is have these tables feed a 2x2 grid, used as a "Task Matrix" of sorts, which would be written specifically for each department to their own standalone spreadsheets which they can leave open, periodically checking in over the course of the day to see what open tasks they have in their area, which would be updated as I update the parent sheet (or the "Task Checklist"). The matrix design I'm using is an adaptation of the "Eisenhower Matrix", and is illustrated in the following screenshot:
https://photos.app.goo.gl/eHAWPKXTQzm9dphP2
I'd like these quadrants to be populated by the two tables which I regularly update, having entries with vlookup = 1 in the upper right hand quadrant, vlookup = 2 in the lower right hand quadrant, and so on. I don't know if this is possible with two tables, so I may have to create a table which pulls data from both of these tables, turns it into one list, and then populates this matrix. However, I'm not entirely certain what my options are, and I'm also somewhat sure that I may have done a poor job at explaining this. If so, please ask and I can elaborate as needed.
_____________________________________________________________________________
UPDATE #1 - CLARIFICATION ADDED BELOW
Here is a snapshot of the sheet that I update regularly. I lost the vlookup functionality when making a copy of the document for reference purposes (which was corrected in Update #2 below), but the rest of the functionality appears to be intact and I'm not sure that the vlookup values are even necessary. Anyway, as you can see, there are different levels of importance to the tasks in the list, and each task has an "Owner". What I want to do, in order to minimize production meetings, is to export the values from the table by "Owner" but to maintain the priority designation on the export. We don't need to see the priority level, or any of the information besides "Action Item" and "Status":
https://photos.app.goo.gl/GQCSgJInmwZl99n13
As for the Task Matrix which I would like to import the info from the tables into, here's a snapshot of that as well. It's a bit naked at the moment, but it can be configured as needed so no worries about formatting suggestions (they're welcome, actually). Basically, anything with a priority level starting with "1" would go into the top right hand quadrant. Anything starting with a "2" would go into the lower right hand quadrant, "3" would go to the upper left hand quadrant, and "R" would go to the lower left hand quadrant. Once again, all I need are the "Action Item" and "Status" to be exported (likely as a string so that only one cell is required), but to have their place in the table dictated by the priority level. "1.1" being the first on the list in the top right, "1.3" being the third on the list, etc., for example:
https://photos.app.goo.gl/eHAWPKXTQzm9dphP2
_____________________________________________________________________________
UPDATE #2 - SO I'VE HAD SOME SUCCESS.....
I've had a small victory with this, but can't figure out the final step to have this skip any "empty" entries and move on to the next row. I've supplied a screenshot of the current status of this, but as for how I reached this point, here is the formula which I've used to pull the data from the parent sheet (changing the 'Task Checklist'!C12=1,2,3, or 6 as needed):
=IF(AND('Task Checklist'!C12=1, 'Task Checklist'!E12="Chemical"), 'Task Checklist'!D12, "")
As for the screenshots of what I'm getting for a result and what got me to this point, here. As you can see, some quadrants are empty because it's counting the empty cells, and overall, they're just barren because they're only searching out the first 9 rows from the parent sheet. Since I have a limited number of rows (I can work with 10 to 15 or so for each quadrant, but at present I'm using 9 since the headers took up one row), so what I need is (in an attempt to put this into one sentence)...
"The first 9 entries that match this criteria"
...if that helps clarify what I'm after. If I can achieve that, I've reached the goal, but to get that done, I need to somehow have the formula ignore any entries that return an empty value (or a '0' if I go that route). Anyway, here are the screenshots. Note that I had to correct the "vlookup" functionality in order to make this formula work:
Parent Sheet ("Task Checklist"): https://photos.app.goo.gl/NdA1qcHy8JRrzdtk1
Task Matrix - Chemical: https://photos.app.goo.gl/OscPOLQiKFOBePwv1
Apologies if this is maybe more confusing than it needs to be. I'm not privy to the lingo used for this sort of stuff so please forgive any confusion that comes from my lack of education on the matter...
So I've searched Google as well as I possibly could without knowing all of the search criteria which may be helpful, but since I made a post about this on Reddit that details the goal that I'm after, I'll simply paste it over to here and go from there. It's uncommon to encounter a lack of responses over there, so I'm left to assume one of two conclusions. Either 1.) I'm unnecessarily complicating things to an extent which can easily be avoided due to a lack of experience with this, or 2.) I'm too much of a beginner to be helped. I'd like to think that it's the former, although neither of those potential causes are anything I'd care to resign myself to
At any rate, here are the details of what I'm trying to accomplish. Please note that I can offer clarification as needed and visual references upon request, if that should help answer the question. Ideally, I'd avoid the use of a macro and instead use a formula. That way, there can be no excuse to not have the table updated in the final spreadsheet that I'm looking to have populated using my own "parent" sheet which I update regularly. In case it wasn't implied from that statement, I'd be using this to delegate tasks to different departments without having to hold a meeting to do it, unless absolutely necessary for critically important matters. However, if the use of a macro is the only way to do this, then so be it.
Link to the original post (the post itself pasted below this, so you can use whichever medium you prefer): https://www.reddit.com/r/excel/comments/84ef4r/table_relationships_question/
Content pasted from the original post:
Note: I've been updating this post with my progress made, which you can find at the bottom. Currently, I've almost solved this, but need assistance on "skipping" empty entries and moving on to the next row.
Before I get into this, I'd like to thank you guys for all your help thus far. I've been attempting to streamline much of the project communication within my department (doing so using Excel), and you've all been a great help with everything I've tried to do. I've added a ton of functionality to one simple spreadsheet and it's becoming a centralized source of information for multiple outlets, which is super helpful because having multiple sources of information leads to more problems than it's worth, at least to my experience. Which leads me to my next request..... having department-specific spreadsheets which are populated by a formula which pulls information from a parent spreadsheet/table which I will be updating as needed.
Basically, my goal is this: I have a "Task Log" which is a table that I update after meetings with my manager, which is broken down by "Priority Level" and "Department" (amongst other things, but these are the important columns for the purpose of this request). The prioritization system built into it is a combination of "priority" and "sequence number", which basically translates out like "1.1" for "priority 1, first in line", then "1.3" would mean "priority 1, third in line" or "3.2" for "priority 3, second in line". It's fairly important that I clearly explain how this system works, so to summarize this, see the associations below:
Priority Level/VLOOKUP Associations:
Note: I have both numbers as well as alphabetical designations for priority levels; 1, 2, and 3 are (perhaps obviously), priorities 1, 2, and 3, respectively. The alphabetical designations on the other hand, are less intuitive. R = Routine, H = Hold, and C = Complete. Since the sorting default that Excel uses wants to go from 1 to 3, then from A to Z, I had to apply a custom sort to ensure that all the "C" designations got kicked to the bottom instead of being in the middle somewhere. It didn't seem to work when using "1 to 3.99, Z to A" as a custom list, so I had to associate each priority level with numbers using vlookup, and custom sort by those numbers. This is why you see the order below.
1 to 1.99 = 1
2 to 2.99 = 2
3 to 3.99 = 3
R to R99 = 6
H to H99 = 5
C to C99 = 4
Custom Sorting Structure:
Sort by: VLOOKUP > Values > "1, 2, 3, 6, 5, 4"
Then by: P > Values > "1 to 3.99, Z to A"
As a side note, I have no idea why "1 to 3.99, Z to A" on the "P" column worked as a secondary sort and not a primary sort. Sort of frustrating, actually...
At any rate, that's the priority level hierarchy which I use for task management, and it works out fairly nicely. I use it for two tables which are side by side, one for "Tasks" which are primarily 'one and done' tasks, and the other for "Projects" which are generally 'ongoing' tasks. What I'd like to do is have these tables feed a 2x2 grid, used as a "Task Matrix" of sorts, which would be written specifically for each department to their own standalone spreadsheets which they can leave open, periodically checking in over the course of the day to see what open tasks they have in their area, which would be updated as I update the parent sheet (or the "Task Checklist"). The matrix design I'm using is an adaptation of the "Eisenhower Matrix", and is illustrated in the following screenshot:
https://photos.app.goo.gl/eHAWPKXTQzm9dphP2
I'd like these quadrants to be populated by the two tables which I regularly update, having entries with vlookup = 1 in the upper right hand quadrant, vlookup = 2 in the lower right hand quadrant, and so on. I don't know if this is possible with two tables, so I may have to create a table which pulls data from both of these tables, turns it into one list, and then populates this matrix. However, I'm not entirely certain what my options are, and I'm also somewhat sure that I may have done a poor job at explaining this. If so, please ask and I can elaborate as needed.
_____________________________________________________________________________
UPDATE #1 - CLARIFICATION ADDED BELOW
Here is a snapshot of the sheet that I update regularly. I lost the vlookup functionality when making a copy of the document for reference purposes (which was corrected in Update #2 below), but the rest of the functionality appears to be intact and I'm not sure that the vlookup values are even necessary. Anyway, as you can see, there are different levels of importance to the tasks in the list, and each task has an "Owner". What I want to do, in order to minimize production meetings, is to export the values from the table by "Owner" but to maintain the priority designation on the export. We don't need to see the priority level, or any of the information besides "Action Item" and "Status":
https://photos.app.goo.gl/GQCSgJInmwZl99n13
As for the Task Matrix which I would like to import the info from the tables into, here's a snapshot of that as well. It's a bit naked at the moment, but it can be configured as needed so no worries about formatting suggestions (they're welcome, actually). Basically, anything with a priority level starting with "1" would go into the top right hand quadrant. Anything starting with a "2" would go into the lower right hand quadrant, "3" would go to the upper left hand quadrant, and "R" would go to the lower left hand quadrant. Once again, all I need are the "Action Item" and "Status" to be exported (likely as a string so that only one cell is required), but to have their place in the table dictated by the priority level. "1.1" being the first on the list in the top right, "1.3" being the third on the list, etc., for example:
https://photos.app.goo.gl/eHAWPKXTQzm9dphP2
_____________________________________________________________________________
UPDATE #2 - SO I'VE HAD SOME SUCCESS.....
I've had a small victory with this, but can't figure out the final step to have this skip any "empty" entries and move on to the next row. I've supplied a screenshot of the current status of this, but as for how I reached this point, here is the formula which I've used to pull the data from the parent sheet (changing the 'Task Checklist'!C12=1,2,3, or 6 as needed):
=IF(AND('Task Checklist'!C12=1, 'Task Checklist'!E12="Chemical"), 'Task Checklist'!D12, "")
As for the screenshots of what I'm getting for a result and what got me to this point, here. As you can see, some quadrants are empty because it's counting the empty cells, and overall, they're just barren because they're only searching out the first 9 rows from the parent sheet. Since I have a limited number of rows (I can work with 10 to 15 or so for each quadrant, but at present I'm using 9 since the headers took up one row), so what I need is (in an attempt to put this into one sentence)...
"The first 9 entries that match this criteria"
...if that helps clarify what I'm after. If I can achieve that, I've reached the goal, but to get that done, I need to somehow have the formula ignore any entries that return an empty value (or a '0' if I go that route). Anyway, here are the screenshots. Note that I had to correct the "vlookup" functionality in order to make this formula work:
Parent Sheet ("Task Checklist"): https://photos.app.goo.gl/NdA1qcHy8JRrzdtk1
Task Matrix - Chemical: https://photos.app.goo.gl/OscPOLQiKFOBePwv1
Apologies if this is maybe more confusing than it needs to be. I'm not privy to the lingo used for this sort of stuff so please forgive any confusion that comes from my lack of education on the matter...
