Hello,
This is my first post looking for assistance. I have found the majority of what I need by searching forums and running Google searches, but I have gotten stuck on a project, and I hope you can help. In general, I want a drop down menu of unique item names which fit multiple criteria, pulled from a constantly updating list of purchases that may not necessarily be unique.
I have a table that contains a number of data entries. For the purposes of my question, I am concerned with the first three columns. 1- a list of items purchased, 2- a category for these items, and 3- a subcategory.
An example of the list of purchases- Monitor, monitor, camera, recorder, camera, monitor, etc.
the Categories are- in house, remote, and storage
the subcategories are - Primary and Accessory
I am trying to create a list of unique items that fit criteria in both the category and subcategory. I want it to read "Monitor, Camera, Recorder" and only list items that are "In House" and "Primary". The result of this list will be used in a drop-down menu that will dynamically adjust as new relevant data is added to the primary list.
first, I have created a table with my primary data entry (Table3, as my sheet refers to it). This table extends dynamically as new items are purchased so they are included in the next step.
I then created a second table (Table4) which uses the following formula:
{=IFERROR(INDEX(Table3[Purchases],MATCH(0,COUNTIF($M$3:M3,Table3[Purchases]),0)),"")} where M3 is the column header for this new table and the list of unique items appears below. I use ctrl+shift+enter to ensure the array formula calculates correctly.
This gives me a list of unique items exactly as I expected, but it also includes items that have categories and subcategories I don't want to include. The items in this list are the named group from which my drop down menu will come.
Is there something I could add to my formula that would only return unique purchased items that are In House and Primary items?
------sub-problem: as new items are added to Table3, and thus to Table4, they appear in the correct column but the Table4 boundry does not dynamically adjust the size for entries that are not manually entered. The result of this is that new items do not appear on the drop down list. To solve this, I have dragged Table4 down a few extra spaces and added the IFERROR function to replace #N/A with blank spaces, and will have to manage the document to ensure there are always blanks for the table to grow in to. is there a better way to do this, so that the drop down list always stays up to date?
I would appreciate any help you could provide, and if any missing information is needed, I will fill in the blanks. Thank you.
This is my first post looking for assistance. I have found the majority of what I need by searching forums and running Google searches, but I have gotten stuck on a project, and I hope you can help. In general, I want a drop down menu of unique item names which fit multiple criteria, pulled from a constantly updating list of purchases that may not necessarily be unique.
I have a table that contains a number of data entries. For the purposes of my question, I am concerned with the first three columns. 1- a list of items purchased, 2- a category for these items, and 3- a subcategory.
An example of the list of purchases- Monitor, monitor, camera, recorder, camera, monitor, etc.
the Categories are- in house, remote, and storage
the subcategories are - Primary and Accessory
I am trying to create a list of unique items that fit criteria in both the category and subcategory. I want it to read "Monitor, Camera, Recorder" and only list items that are "In House" and "Primary". The result of this list will be used in a drop-down menu that will dynamically adjust as new relevant data is added to the primary list.
first, I have created a table with my primary data entry (Table3, as my sheet refers to it). This table extends dynamically as new items are purchased so they are included in the next step.
I then created a second table (Table4) which uses the following formula:
{=IFERROR(INDEX(Table3[Purchases],MATCH(0,COUNTIF($M$3:M3,Table3[Purchases]),0)),"")} where M3 is the column header for this new table and the list of unique items appears below. I use ctrl+shift+enter to ensure the array formula calculates correctly.
This gives me a list of unique items exactly as I expected, but it also includes items that have categories and subcategories I don't want to include. The items in this list are the named group from which my drop down menu will come.
Is there something I could add to my formula that would only return unique purchased items that are In House and Primary items?
------sub-problem: as new items are added to Table3, and thus to Table4, they appear in the correct column but the Table4 boundry does not dynamically adjust the size for entries that are not manually entered. The result of this is that new items do not appear on the drop down list. To solve this, I have dragged Table4 down a few extra spaces and added the IFERROR function to replace #N/A with blank spaces, and will have to manage the document to ensure there are always blanks for the table to grow in to. is there a better way to do this, so that the drop down list always stays up to date?
I would appreciate any help you could provide, and if any missing information is needed, I will fill in the blanks. Thank you.
