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.

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.

-- 
Daryl S


"QChris" wrote:

> 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.

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



"Daryl S" wrote:

> 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.
> 
> -- 
> Daryl S
> 
> 
> "QChris" wrote:
> 
> > 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.

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??

"QChris" wrote:

> 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.