Lists, lookup, if???

[QChris]

Hi,

A new user to this site. Been learning a lok lately, but stuck now!

I have a sheet where i want to look for "value1" to "value10" in columns I,
K, M, O.

Once a particular value is found, I want it to display the value (numerical)
in the right hand column, J, L, N, P.

But I also want it to count how many parts are require by looking at the
number enterred in the same row but column D.

Basically, it's a cutting list with different profiles, lengths and
quantities and I'd like a summary of each profile, with each cut length
required and quantity required.

On a really, really, really, complicated matter, is it possible to write a
formula to auto select these in best material usage, i.e. stock material
lengths are 4m, 4.25, 4.8m?

Part 1 is the icing, part 2 is the cherry.

 

[Daryl S]

QChris -

Say you put the value1 in cell K1 and value10 in cell L1, and your data
starts on row 2. Then put this in cell L2:

=IF(AND(K2>=$K$1,K2<=$L$1),K2,"")

This will cause cell L2 to show the value of cell K2 if it is between the
values in cells K1 and L1. You can copy/paste or drag this cell down the
column and then copy/paste the values into columns J, N, and P. I am
assuming they all use the same value1 and value10 you put in cells K1 and L1.
If not, you can adjust the formula for each column before copying it down.

That's the icing. I am not sure what your cherry request is or where the
data you would use is to help with that one.

 

[QChris]

Hi Daryl

Sorry for my stupidity, but I cannot get that to work. I'm on a steep
learning curve with excel at the mo.

I've put an example of the table I'm using:

Col B Col D Col E Col F Col G Col H Col I Col J Col K
qty section length section length section length section length

5 A 2200 B 1800 B 2000 C 654
3 I 2200 B 1800 G 1587 C 254
7 A 2200 B 2100 B 2000 C 300
30 F 2200 B 1800 G 2000 C 254
etc.


So, I'm trying to list each section (there are 10) with each different
corrsponding cut length plus the quantity of the cut lengths required, noting
the assembled quantities in Col B.

The above info is manually input so the section,length and quantity can all
vary.

I dont know if this helps or makes the whole request a nightmare!

Ultimately, once I have a cutting list, I then want to best material usage
when cutting from stock lengths. There are 3 stock lengths. (This was the
cherry) but it all looks like a bit of a nightmare to me?

Thanks

 

[QChris]

This is the table layout I was going to use to display results:
I've entered results, 2100 etc, but it's a clever formula I'd like to go in
there.

Col I Col J
SECTION 1
Total parts 51 (i've got this formula)
length qty
2100 32
569 10
235 3
234 6
etc.

Does this help??