Dec 12 2022 11:00 AM - edited Dec 12 2022 01:06 PM
I'm trying to pull in multiple rows of data based off of a data validation list from a single cell. For example, if I have fifteen pieces of data attached to a single input, I would like for that single input to pull in those fifteen pieces of data.
I am creating a sort of documentation tracker for a training platform that I manage for my company. The documentation is attached to what is called a Learning Pack Item (LPI), and the LPI is attached to a Learning Pack, which is then attached to a Skill. The reason I'm building this tracker is to keep all document and LPI:LP data in one place. Learning Packs can be attached to many skills, and I really don't want to have to remember to make changes to multiple skill sheets in my workbook when I add/remove/edit a Learning Pack or LPI.
I currently have my Skills on different sheets, and all the data for the Learning Packs on their own data sheet. I have the Learning Packs tied to a list. I want to be able to select the Learning Pack from the list, and have the Learning Pack Item field populated with all LPI's attached to the Learning Pack. I found a weird, long work around when I was playing around with this that involves a series of nested =IFs. I also found out that you can only nest 64 formulas at once. The problem is I could have HUNDREDS of learning packs by the time I'm done building this platform.
I've attached a few images. The first image is a Skill Sheet, the second image is my Learning Pack/Learning Pack Item data, and the third image is a piece of the formula I've written. Any help condensing that beast would be MUCH appreciated.
Edit: Adding a link - spreadsheet
Dec 12 2022 12:27 PM
Is it possible for you (without violating company confidentiality) to post a copy of the actual workbook on OneDrive or GoogleDrive? Then post a link here that grants access and editing privileges to that shared file.
I ask because I strongly suspect that there IS a far simpler way to do this--as you seem to suspect as well--but I'm having a hard time visualizing what your Data! sheet/table looks like. Certainly one concern I'd have, and you should have as well, is that you appear to be "hard coding" in some references to regions on that Data! sheet to some sub-categories of Learning Packs, and (as a former corporate trainer myself) I can well imagine those regions growing and contracting over time, so hard-coding like that is going to create problems as Learning modules/Packs in a particular category increase or decline in number.
It would be far easier to help, though, if we could see the full deal, rather than a couple of selected images.
If the full sheet is or needs to remain confidential, perhaps an abbreviated mock-up that gives a more complete picture. It would also be helpful if it could include edit access to an actual workbook, not just images. Having only images requires any of us who'd like to help to recreate a working example, when you already have one!
Dec 12 2022 01:07 PM
Dec 12 2022 02:53 PM
Solution
The Google Sheet was "View only," but fortunately it does permit downloading to Excel, so I did that and edited in in Excel. I've attached what I am guessing to be what you would like to do. But it's a guess.
A couple of observations: the primary one being that you've made what I consider to be a mistake, in that you've paid a lot of attention to making things "look sexy" when you haven't yet figured out the basic functionality. And that very effort--focus on appearance--can get in the way of functionality. So as a general rule in design, I suggest you concentrate on making it work first; then look to the "looks" of it.
A related suggestion would be that--especially if you're going potentially to be adding items in greater quantity--you might be better off doing the extraction on separate pages for each category. [In this existing workbook, for example, if you were to create more than 15 "learning Pack Items" under the category of "Boring" you'd be running into a #SPILL problem when it hits the first of the Fun Pack rows.
You might want to consider creating a single "dashboard" sheet where you specify the category and then extract the relevant items. Depends really on your objective with this. If it's to track individual completions of courses, for example, that would dictate one kind of design, whereas if your objective is to catalog all courses, .....
Dec 13 2022 06:07 AM
Dec 13 2022 02:37 PM - edited Dec 13 2022 02:41 PM
select count(*) 行,Learning_Pack,group_concat(Learning_Pack_Item),group_concat(Level),group_concat(Document_Type),group_concat(Document_Name),group_concat(Approval_Required),group_concat(Content_Createor),group_concat(Owner),group_concat(Accountable),group_concat(Expiry),group_concat(Location) from filter_merge_cells_report group by Learning_Pack;
select * from filter_merge_cells_report limit 5;
with database structure,can output merge cell report filtered with any keyword by sql easliy.
Dec 12 2022 02:53 PM
Solution
The Google Sheet was "View only," but fortunately it does permit downloading to Excel, so I did that and edited in in Excel. I've attached what I am guessing to be what you would like to do. But it's a guess.
A couple of observations: the primary one being that you've made what I consider to be a mistake, in that you've paid a lot of attention to making things "look sexy" when you haven't yet figured out the basic functionality. And that very effort--focus on appearance--can get in the way of functionality. So as a general rule in design, I suggest you concentrate on making it work first; then look to the "looks" of it.
A related suggestion would be that--especially if you're going potentially to be adding items in greater quantity--you might be better off doing the extraction on separate pages for each category. [In this existing workbook, for example, if you were to create more than 15 "learning Pack Items" under the category of "Boring" you'd be running into a #SPILL problem when it hits the first of the Fun Pack rows.
You might want to consider creating a single "dashboard" sheet where you specify the category and then extract the relevant items. Depends really on your objective with this. If it's to track individual completions of courses, for example, that would dictate one kind of design, whereas if your objective is to catalog all courses, .....