MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Lists, lookup, if???

• Follow

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

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

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

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

3 Replies
128 Views

Similiar Articles:

7/28/2012 4:41:02 AM