Hi,
I've been looking for a new solution on a similar pricing model to the one I was working on 2 weeks ago. In this one, I have a 1300 record database and use the Filter function to bring in the records that are related to user-selectable parameters (model, module, workscope). Here's an example of the Filter Function that resides in a destination price sheet (sheet3, formula in cell A36)
=IFERROR(FILTER(PLM_Output,(Model_Col=$B$8)*(Module_Col=$J$2)*(Workscope_Col=$J$3)*(PLM_Col="Parts")*(PartsCat_Col="SLL")*(Price_Logic_Col=TRUE)),999)
(fyi, The Iferror function on the outside allows me to conditional format a 999 result with white text (invisible). Ideally, I might eventually use the Hide_Rows VBA that Kevin9999 helped me with)
What I want to do now, is have the ability for knowledgeable users to select a "detail" mode on a main parameters sheet (sheet2), which will copy the Filter function results (either values or original database formulas) and paste into a separate sheet (I've created a new one, e.g. Sheet7 in my development file). I intend to unprotect certain cells on that sheet (namely Quantity) and allow those users to edit the Qty to change the pricing extensions and totals.
Here is an example of 2 rows of spilled filter results in sheet3 (out of 4 max in this instance)
I've looked a few earlier responses, such as this one Copy/Paste Values of Results of FILTER function but haven't been able to get them to work. That example is initiated by double-click rather than worksheet change, which I suppose is an option for me if I could get it working)
TIA
Gary (bertible)
I've been looking for a new solution on a similar pricing model to the one I was working on 2 weeks ago. In this one, I have a 1300 record database and use the Filter function to bring in the records that are related to user-selectable parameters (model, module, workscope). Here's an example of the Filter Function that resides in a destination price sheet (sheet3, formula in cell A36)
=IFERROR(FILTER(PLM_Output,(Model_Col=$B$8)*(Module_Col=$J$2)*(Workscope_Col=$J$3)*(PLM_Col="Parts")*(PartsCat_Col="SLL")*(Price_Logic_Col=TRUE)),999)
(fyi, The Iferror function on the outside allows me to conditional format a 999 result with white text (invisible). Ideally, I might eventually use the Hide_Rows VBA that Kevin9999 helped me with)
What I want to do now, is have the ability for knowledgeable users to select a "detail" mode on a main parameters sheet (sheet2), which will copy the Filter function results (either values or original database formulas) and paste into a separate sheet (I've created a new one, e.g. Sheet7 in my development file). I intend to unprotect certain cells on that sheet (namely Quantity) and allow those users to edit the Qty to change the pricing extensions and totals.
Here is an example of 2 rows of spilled filter results in sheet3 (out of 4 max in this instance)
I've looked a few earlier responses, such as this one Copy/Paste Values of Results of FILTER function but haven't been able to get them to work. That example is initiated by double-click rather than worksheet change, which I suppose is an option for me if I could get it working)
TIA
Gary (bertible)
