I have an existing workbook that I created and need to improve on. Please see attached.
My problems are:
1. the formula in J10:J165 is sometimes returning a "false" response to formulas, where I want a zero value returned. The formula reads columns D & E, which are width and height values of a sheet of wood. the formula should calculate stiffeners based on these dimensions. any time a sheet is larger than 24" by 48", stiffeners need to be placed at 24" on center along the longer dimension. I think what has me messed up is that you have to make the formula understand that it can be larger than 24" wide by 48" high OR 48" wide by 24" high. I seem to have the formula working for all scenarios except when the width dimension is less than 48 and the height dimension is less than the width dimension.
For examples of how it should work, any time the dimensions are greater than 24 by 48, the long dimension is divided by 24.02 and rounded down to the nearest whole number:
W:23 H:47 - Stiffeners = 0
W:24 H:48 - Stiffeners = 1
W:48 H:24 - Stiffeners = 1
W:56 H:36 - Stiffeners = 2
W:47 H:23 - Stiffeners = "FALSE"
W:48 H:25 - Stiffeners = 1
2. Columns G & H, "stretch width" and "stretch height", are just the dimensions discussed previous, plus 2. these dimensions tell me what size stock I need to cut the actual panel out of. there are limitations to what size stock is available for different types of wood. So if I am using a type of wood that comes in a maximum size of 48" x 96", I want to input those dimensions in the "stock sheet width" and "stock sheet height" cells. Then, when I am entering the "face width" and "face height" dimensions in the table below, if any sizes come up larger than the stock material available, I want that entire row to highlight in yellow.
3. Sometimes the wood grain direction will affect the finish of the product, and all of the wood grain will be required to go in the same direction. In this case, I want to select "yes" in the "grain direction matter?" cell, and indicate "height" or "width" in the "grain direction" cell. Then, any time a "stretch width" or "stretch height" dimension exceeds the stock sheet dimensions, I need that row to highlight in yellow.
If anyone is willing to give me a hand I would really appreciate it. Hopefully what I have written is clear enough, but if not I would be happy to explain further as needed, even call if you wish. I know enough to get by okay, but this is getting a bit out of my realm of understanding at this point, though I know it can be done.
also, I prefer formulas that I can lock the cells, rather than Macro, VBA, etc. Some of my clients that will be using this need it to be very simple input only. If they have to enable macros or anything of that sort, they will just not use it unfortunately.
Bookmarks