if statement calculation base on a list of words
if statement calculation base on a list of words
(OP)
Hi all, I need some help. I am trying to automate a BOM creation task. We have three columns, extended qty, actual qty, and description. I also have a list of hardware that details how many extra get put in hardware bags based on a type. I would like to create a nested if statement that will add the correct extra qty to the extra qty column based on what word the description cell starts with.
Any ideas?
Any ideas?
Kind Regards,
Brian





RE: if statement calculation base on a list of words
Kind Regards,
Brian
RE: if statement calculation base on a list of words
Your table should look like this:
Use the IF to determine which column of the table to use.
Use Contains()
Use Or()
RE: if statement calculation base on a list of words
The attached is one of the formulas I am currently using.
Kind Regards,
Brian
RE: if statement calculation base on a list of words
Kind Regards,
Brian
RE: if statement calculation base on a list of words
Kind Regards,
Brian
RE: if statement calculation base on a list of words
1) state your requirements, clearly concisely and completely
2) upload a representative workbook, rather than a nearly worthless picture
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: if statement calculation base on a list of words
In the future, I would be best, if the OP does not give you the information, request it without being a complete ass. JUST SAYIN. Or, just move on.
RE: if statement calculation base on a list of words
User makes an initial selection from a drop down, or they enter a selection. Then the indirect function allows you to look up additional values based on the initial selection.
RE: if statement calculation base on a list of words
Here's your workbook back with these additions:
1) a consolidated lookup table
2) lookup range names
3) a new consolidated lookup formula
B4: =INDEX(OFFSET(Item,MATCH(LEFT(E4,FIND(" ",E4)-1),Item,0)-1,2,COUNTIF(Item,LEFT(E4,FIND(" ",E4)-1)),1),MATCH(C4,OFFSET(Item,MATCH(LEFT(E4,FIND(" ",E4)-1),Item,0)-1,1,COUNTIF(Item,LEFT(E4,FIND(" ",E4)-1)),1),1),1)+C4
Skip,
for a NUance!
RE: if statement calculation base on a list of words
The only issue I have with your solution, it removes the cell borders once the formula completes the calculation. Is there any way to stop that? I know that its easy enough to go in a put the borders back in, but when you are dealing with a BOM that has several hundred components, it would be nice if it was complete when the information is copy & pasted.
Kind Regards,
Brian
RE: if statement calculation base on a list of words
RE: if statement calculation base on a list of words
A formula alone, cannot change formatting. COPYING a cell that contains a formula and PASTING it in a cell with some other format than the source cell, will, indeed, change the format of the target cell.
In order to avoid "infecting" the target cell's format, use Paste Special--FORMULAS. Conditional Formatting would have nothing to do with pasting formulas.
Skip,
Just traded in my OLD subtlety...
for a NUance!