looking up portions of a list

  • Thread starter Thread starter Dave W
  • Start date Start date
D

Dave W

I have a bid worksheet that has a column titled "material
groups" and another column titled "material description".

I also have a worksheet that is actually just a list.
the first col is titled "material groups", the second col
is titled "material description", the third col is
titled "price", the fourth col is titled "unit of
measure".

What I would like to do is have the person who is working
on the bid worksheet pick from a validated list box the
specific material group the mat is associated with, which
would limit the choices of materials they have to pick
from. We have a lot of materials that we use and it gets
time consuming scrolling through the entire list of
materials.

Can anyone other a direction, combinations of functions
etc.

thanks in advance
 
...
...
What I would like to do is have the person who is working
on the bid worksheet pick from a validated list box the
specific material group the mat is associated with, which
would limit the choices of materials they have to pick
from. We have a lot of materials that we use and it gets
time consuming scrolling through the entire list of
materials.
Click to expand...
...

Simplified example. Let's say you had a table like the following named Tbl.

Grp Item
A M1
A M2
A M3
B M4
B M5
B M6
B M7
C M8
C M9

Create another table named GrpRef with the first column containing the distinct
groups, which could be produced by Data > Advanced Filter. It'd look like

Grp O N
A 1 3
B 4 4
C 8 2

The O value for Grp A is always 1, and the N value is

=COUNTIF(INDEX(Tbl,0,1),INDIRECT("RC[-2]",0))

Fill this formula down as far as needed. The O value for Grp B is given by

=INDIRECT("R[-1]C",0)+INDIRECT("R[-1]C[+1]",0)

Fill this formula down as far as needed. Then you could create a dymanic named
range to serve as the material drop-down validation list depending on the
material group entry in GrpEntry using the formula

=OFFSET(Tbl,VLOOKUP(GrpEntry,GrpRef,2,0),1,VLOOKUP(GrpEntry,GrpRef,3,0),0)
 
Back
Top