jeff.kisko Posted June 19, 2019 Share Posted June 19, 2019 Looking for some advice here. I'm trying to convert more data collection over to the worksheets and keep it as automated as possible. What is the best workflow to add in pricing that can vary depending on the material used? Say for a graphic panel, I pull the geometry to get width + height, but the price will change if the panel is PVC or fabric or Plexi, etc. I have a record format set up so that I can describe the material in the worksheet, but is there a way to multiply the dimensions by the type of material being used? Currently I have a manual entry on the record, also considered a different header line per material.. but that just adds a bunch of extra lines. Thanks! Quote Link to comment
Pat Stanford Posted June 19, 2019 Share Posted June 19, 2019 If you have a small number of options you could use nested IF statements to look up the price per unit in either the same worksheet or a different worksheet. =IF(Object.Material='Plexi',$B$3,IF(Object.Material='Cloth',$B$4,IF(Object.Material='Wood',$B$5,'Unknown Material') Where Object.Material is the record.field that holds the material data. If you already have it in a worksheet/database cell you could just reference the cell also. If you have too any options for a set of nested IFs (more than 3 or 4), then a Worksheet Script could be created that would read data, probably easiest form a separate worksheet of prices. You would pass the script the name of the worksheet, the column with the reference value and the column with the data to return. That way you would only have to keep the data up to date in one place. The down side being that the data would not be stored with the object and you could not easily overwrite the value for a single object. Ask again if you need more help. Quote Link to comment
jeff.kisko Posted June 20, 2019 Author Share Posted June 20, 2019 Thanks Pat, I was curious if the IF command would be an option, but I sent too sure on the formula. I’ll try this fo part of the worksheet. We deal with at least a dozen material options, so the separate worksheet may be the better route for this part. Thanks again! jeff Quote Link to comment
Peter Vandewalle Posted June 20, 2019 Share Posted June 20, 2019 This worksheet script may help you to find the data from a list. It works like the vlookup function in most worksheet softwares. WSVLookup.zip Quote Link to comment
Pat Stanford Posted June 20, 2019 Share Posted June 20, 2019 Thanks @Peter Vandewalle I thought I was going to have to search the archives for this 😉 Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.