Feb 05 2021 10:33 PM
Feb 05 2021 10:33 PM
I have been trying to figure this out for a few days now and I'm not getting very far. Essentially this is the situation. I'm a huge orchid collector and before my collection get's much bigger, I wanted to start getting organized. So I am trying to get a workbook together. I know what I want it to do, but I can't remember how to do it. I don't know how I graduated school sometimes. lol
Anyway, "Complete List" worksheet is where I put in all of my orchids, when I purchased them, when they were shipped, and when they were received. How much I spent, the shipping, the seller, the orchid ID, etc. I have all of my receipts all of the way back to 2018. So I need to have that "Complete List" worksheet automatically filter into separate worksheets, which I'm not finished fully plotting out, but I have the years there. So I want to at least get that started. From there, I may move to filtering them to types of orchids.
Anyway, if someone can help me with how to get them to automatically filter to these worksheets within the workbook without the help of a button, that would be great. If a button is absolutely necessary, I am fine with that, but what I absolutely need, is for the formula to not allow for duplicates.
I really appreciate you all on this site.
OH! And right now I'm working on a windows 10 PC, but I originally started the file on my mac mini at work that runs High Sierra. If you need more information than that, just let me know.
Feb 05 2021 11:18 PM
@FrameofLightDesigner Why go through all the trouble of putting everything into one list and then split it all up by year, basically duplicating the information.
Consider sticking to one list, put filter buttons on the header row. Since the dates in the list are proper dates, you can now click on the "down" arrow and select/deselect the year(s) you want to see. Same thing for all the other columns. You could, for instance, filter all orchids of the same genre, or everything bought from one particular seller.
In the attached file, I transformed your Complete list to a structured table. I re-created your list on a Mac and it will work the same on a PC. Want to add a new item? Select L165 and press Tab. A new row is inserted. Formulae for Item number and Total are copied down automatically. Alternatively, select any cell on row 165. Right-click, Insert, Table Row Below.
More about structured tables in the link below.
Feb 08 2021 09:44 PM
@FrameofLightDesigner Possible. Where and how do you intend to keep the "care" information? And when you talk about "types" of orchids, do you then refer to "Genre"? If so, you are dealing with 82 unique genres in the table. That's quite a lot of data labels if you want to capture all of them in a graph.
Anyhow, I've added a pivot table and a graph with a slicer and a timeline, just to demonstrate the principle. Not very pretty, though, and probably not very meaningful either. Have a look and see if this is something worth pursuing.
Feb 09 2021 07:48 AM
@Riny_van_Eekelen Sir! You have some serious skills! I love your excel style! The one thing I need to do is input the proper care information for each orchid type so that the "average" can be automated. The one thing I'm struggling with, with that, is if I should do that by tab? Or would a table be best? I'm thinking table, because the headings are what the graph would pull from. If I'm correct, I can make a new worksheet called "Orchid Care" and then create a table with the proper headings and input the proper information. I say a new worksheet because I know it will help with organization and cleanliness. I know it's not completely necessary.
Feb 09 2021 08:21 AM
@FrameofLightDesigner If you want to use this system only on a Windows PC, I guess you could create a separate table with the care information for each type (Genre) of orchid, and load both tables into the Data Model. From there you could create (a) pivot table(s) that draws information from both. But, it's difficult to visualise/explain without the data on hand.