Hunter_001
New Member
- Joined
- Apr 21, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi All,
I am trying to create a quote and have run into a snag.
I have a product database on a separate sheet (ProductDB) and have to extract the data from there to the quote (Form) based on a drop down menu in the quote line (cell value).
(I posted the DB sheet below and the output sheet (Form) with an arrow to better explain what it should look like (ideally)).
I also need to disable/delete the dropdown menu in Col C if there is data in Col D.
I've tried everything I can think of, but don't want to put complicated formulas in the cells.
The best I came up with is to use Offset in cell Form!D19 from a different database (PACKAGEDB) where the package name and items are assembled using:
=CONCATENATE("Build-your-own-package - SMALL PACKAGES & BABY1 PACKAGES",CHAR(10),"Item 1",CHAR(10),"Item 2",CHAR(10),"Item 3",CHAR(10),"Item 4"
In green is what I managed with:
=IF(LEN(VLOOKUP(C19,PACKAGEDB!$A$2:$F$24,2,FALSE)>0),OFFSET(PACKAGEDB!A2,0,1,1,7),"")
Basically what I want to do is to use the dropdown in C19 and cycle through the ProductDB until all matches from ProductDB based on ProductDB!D:D (PLAN) cell content are extracted to the quote sheet.
The "item code" can display for the zero value items as well if it cannot be avoided. Some products from the ProductDB will be applicable to more than one plan.
I also want to override the dropdown menus in C
If anyone could assist me here with a VBA/formula to do this, I would be forever grateful.
P.s. There are a bunch of other macros and VBAs on the sheet to reset areas, lock/unlock, insert rows etc., but this one I cannot crack.
With OFFSET I cannot get the items to display in separate rows as INDEX, FIND, MATCH, COUNTIF & LEN combinations etc. can only give me the number of lines - not specific lines that contain the data.
File is available - not sure if I can place a link to a Dropbox? I need to redo the "reset form" sheet in correct format/columns once I have figured out the issue at hand.
TIA
I am trying to create a quote and have run into a snag.
I have a product database on a separate sheet (ProductDB) and have to extract the data from there to the quote (Form) based on a drop down menu in the quote line (cell value).
(I posted the DB sheet below and the output sheet (Form) with an arrow to better explain what it should look like (ideally)).
I also need to disable/delete the dropdown menu in Col C if there is data in Col D.
I've tried everything I can think of, but don't want to put complicated formulas in the cells.
The best I came up with is to use Offset in cell Form!D19 from a different database (PACKAGEDB) where the package name and items are assembled using:
=CONCATENATE("Build-your-own-package - SMALL PACKAGES & BABY1 PACKAGES",CHAR(10),"Item 1",CHAR(10),"Item 2",CHAR(10),"Item 3",CHAR(10),"Item 4"
In green is what I managed with:
=IF(LEN(VLOOKUP(C19,PACKAGEDB!$A$2:$F$24,2,FALSE)>0),OFFSET(PACKAGEDB!A2,0,1,1,7),"")
Basically what I want to do is to use the dropdown in C19 and cycle through the ProductDB until all matches from ProductDB based on ProductDB!D:D (PLAN) cell content are extracted to the quote sheet.
The "item code" can display for the zero value items as well if it cannot be avoided. Some products from the ProductDB will be applicable to more than one plan.
I also want to override the dropdown menus in C
If anyone could assist me here with a VBA/formula to do this, I would be forever grateful.
P.s. There are a bunch of other macros and VBAs on the sheet to reset areas, lock/unlock, insert rows etc., but this one I cannot crack.
With OFFSET I cannot get the items to display in separate rows as INDEX, FIND, MATCH, COUNTIF & LEN combinations etc. can only give me the number of lines - not specific lines that contain the data.
File is available - not sure if I can place a link to a Dropbox? I need to redo the "reset form" sheet in correct format/columns once I have figured out the issue at hand.
TIA
