Hello,
I'm intermediate with Excel formula but not very experienced with VBA. Any guidance on the below would be much appreciated!
I've built a spreadsheet that will help my colleagues add/remove product codes that are used by a client. I need this because there is a specific set of product codes that must be applied to a client to activate a service in our internal system. If the client has, say, 20 product codes currently and needs to remove 10 of them add 15 others to activate this service, it can be a time consuming process to do manually.
Here's my solution so far:
Steps:
1. My colleague must first select a product platform from a master list. This has been set up as a defined name range. Each product platform has slight variations in product codes to activate the service. Example: The client uses the "Institutional" platform which has 4 different product codes that "Retail".
2. My colleague will enter the 5 digit product codes in Sheet 1, column A whereby column B pulls in the name of the product through a VLOOKUP from Sheet "X" (which contains the product codes that only apply to the specified platform)and column C returns them an action based on a vlookup/iferror function. For example, "ABCDE" is entered in A1, B1 return "Alpha Beta Product" from Sheet 2 which contains the product codes for "Institutional" only and C1 returns "remove this product".
3. This is where I could use some help! After my colleague enters in the existing product codes, I want the spreadsheet to tell them below this area what additional product codes need to be added. I've set up each sheet that contains the platform product codes with a vlookup so that if the product code is on Sheet 1 in column A, the product code will be returned. If the product code is not listed in Sheet 1, column A, the phrase "Need To Add" has been populated. Example: on the "Institutional" platform sheet, the vlookup returns "ABCDE" in column c because it is listed on Sheet 1, column A.
Now, how would I pull in the product codes for the investments that populate "Need To Add"? There are two parts to it:
1. The macro must take into account which platform is being used so to know which spreadsheet to draw the data from.
2. The macro then needs to pull in only the product codes on the platform specific sheet that have "Need To Add".
As the title mentions, I've considered pulling in all platform-specific product codes onto Sheet 1 via excel formula and then creating a macro that hides any rows that <> "Need To Add".
Any thoughts would be helpful! Even a different way of going about doing this.
I'm intermediate with Excel formula but not very experienced with VBA. Any guidance on the below would be much appreciated!
I've built a spreadsheet that will help my colleagues add/remove product codes that are used by a client. I need this because there is a specific set of product codes that must be applied to a client to activate a service in our internal system. If the client has, say, 20 product codes currently and needs to remove 10 of them add 15 others to activate this service, it can be a time consuming process to do manually.
Here's my solution so far:
Steps:
1. My colleague must first select a product platform from a master list. This has been set up as a defined name range. Each product platform has slight variations in product codes to activate the service. Example: The client uses the "Institutional" platform which has 4 different product codes that "Retail".
2. My colleague will enter the 5 digit product codes in Sheet 1, column A whereby column B pulls in the name of the product through a VLOOKUP from Sheet "X" (which contains the product codes that only apply to the specified platform)and column C returns them an action based on a vlookup/iferror function. For example, "ABCDE" is entered in A1, B1 return "Alpha Beta Product" from Sheet 2 which contains the product codes for "Institutional" only and C1 returns "remove this product".
3. This is where I could use some help! After my colleague enters in the existing product codes, I want the spreadsheet to tell them below this area what additional product codes need to be added. I've set up each sheet that contains the platform product codes with a vlookup so that if the product code is on Sheet 1 in column A, the product code will be returned. If the product code is not listed in Sheet 1, column A, the phrase "Need To Add" has been populated. Example: on the "Institutional" platform sheet, the vlookup returns "ABCDE" in column c because it is listed on Sheet 1, column A.
Now, how would I pull in the product codes for the investments that populate "Need To Add"? There are two parts to it:
1. The macro must take into account which platform is being used so to know which spreadsheet to draw the data from.
2. The macro then needs to pull in only the product codes on the platform specific sheet that have "Need To Add".
As the title mentions, I've considered pulling in all platform-specific product codes onto Sheet 1 via excel formula and then creating a macro that hides any rows that <> "Need To Add".
Any thoughts would be helpful! Even a different way of going about doing this.
