if statement calculation base on a list of words 1

[BLONG37]

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?

Kind Regards,

Brian

 

[BLONG37]

Here is the extra qty table I have to use.

Kind Regards,

Brian

 

[MintJulep]

Use a table and VLookup to get the extra quantity.

Your table should look like this:

[pre]Actual Bolts Screws
1 0 0
5 1 1
15 1 2
20 2 2
30 3 2
40 4 2

[/pre]

Use the IF to determine which column of the table to use.

Use Contains()

Use Or()

 

[BLONG37]

I don't think that is exactly what I am looking for.
The attached is one of the formulas I am currently using.

Kind Regards,

Brian

 

[BLONG37]

Here is the other formula.

Kind Regards,

Brian

 

[BLONG37]

I need a way for excel to pick the correct formula based on the first word in the description.

Kind Regards,

Brian

 

[SkipVought]

If you really want help...

1) state your requirements, clearly concisely and completely

2) upload a representative workbook, rather than a nearly worthless picture

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BLONG37]

SkipVought, read my OP. I believe I stated my needs quite clearly. As for the pictures, the cell with the formula is selected so the formula shows in the formula bar. Just to appease the masses, I am attaching a mock BOM.

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.

 

[JoelTXCive]

BLong - Have you tried the "Indirect" function. Look for videos on youtube. The indirect function combined with lookup tables can be very helpful in situations like this.

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.

 

[SkipVought]

Sorry, there are no IFs in this solution.

Here's your workbook back with these additions:

1) a consolidated lookup table
2) lookup range names
3) a new consolidated lookup formula
[tt]
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
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BLONG37]

Skip,
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

 

[BLONG37]

Never mind, I figured it out with conditional formatting. Thanks all for looking.

 

[SkipVought]

The only issue I have with your solution, it removes the cell borders once the formula completes the calculation.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]