Conditional Logic


Looking to build a option where my end users can choose a Data Validation Field drop down in the sheet called "PaperCut Initial Options" and choose 1 of 2 options for Commercial/Legal, Education/Gov't/NonProfit.   Then answer another question below it of Select the Manufacturer provider and answer Xerox or HP.  Based on those two answers populate the proper information in the sheet SPW - Internal for the cell C17 for the proper description.   The proper description would then pull the sheet information for Product Database for either cell A8, A9, A10, A11 for the Commercial Xerox, Commercial HP, Education Xerox or Education HP option.  I will then map the pricing based on that from the table to the SPW - Internal sheet based on the Description to then pull the item #, Financial Cost, Salesperson Cost, MSRP, Suggested Price from the Product Database.  Can you help me with the logic behind this and do it in this example file?




 Your fear not to have done enough planning is well founded. You shouldn't progress any further without finalising the planning.

Basically you need a database of "Sales Items". From this DB you create sales orders, order confirmations, invoices and perhaps commission credits, always accessing the same data. Your tab SPW-Internal seems to aim at this position in your system but then you seem to diverge.

A sales order should be created from a suitable template into which one or more Sales Items are copied and then summed up. You would need a DB for sales orders in which you record its date, who created it, and which sales items were included in it. With the help of this DB copies can be re-created.

All of the above can't be pressed into one question on this forum. In fact, a flow and questions and answers should ensueas you progress. For the purpose of this forum each question must be in a separate thread which is closed down when an answer has been found.

For the moment, since you are asking about logic and layout, I don't think that your validations are either good or workable. In the attached copy of your project I have added a tab "Params" (for "Parameters") which could be hidden eventually. On this tab I created a table for each validation dropdown. I then changed the reference in the cell validations to refer to these tables. You will no longer see the blank rows you now have while being able to easily modify the lists including making additions to them or deletions.

Referencing a table for data validation isn't straight forward. You need to use the INDIECT function and then a structured reference. In the example below "Type" is the name of the table containing the list.


Another thing is your use of external worksheets which you reference in formulas. These are two no-nos in one cigar. I wouldn't reference external worksheets in formulas, and I wouldn't use workbooks on a cloud for this purpose. This is because I live in an environment where access to clouds is always inhibited by slow internet connections and access to external files is always inhibited by permissions over which I have insufficient control. Usually, if there are a sufficient number of people using a project to make using a cloud and external files worth considering there also is a constant flow of innovation and introduction of errors - somebody has a new computer and suddenlay can't access the external files (or the cloud) for indiscernable reasons, or someone installs a sleek computer game on his terminal which upsets his settings. Something like that always happens. Forums like this one are full of calls for help in such instances.

I prefer to have all data a system needs in one workbook and deal with the problem of how to keep it up todate as the only problem I will ever have.


Answer the Question

You must create an account to use the forum. Create an Account or Login