Trouble Designing a Lookup

I'm not faint at heart when it comes to Excel, but I can't figure out
how to do what I need.

Here it is:

I have a grid structured as follows (Dates on x axis and products on y
with quantity in yards due listed at the intersections):

+----+-----------------+
| Due Date |
+-------+----+----+---+---+----+
|Product|5-19|5-26|6-2|6-9|6-16|
--------+----+----+---+---+----+
| A |Qty |Qty |Qty|Qty|Qty |
--------+----+----+---+---+----+
| B |Qty |Qty |Qty|Qty|Qty |
--------+----+----+---+---+----+
| C |Qty |Qty |Qty|Qty|Qty |
--------+----+----+---+---+----+

Now I have this table (Same exact dates on x axis with material required
on y, with quantity in lbs. required at the intersections):

+----+-----------------+
| Due Date |
+---------+----+----+---+---+----+
|Materials|5-19|5-26|6-2|6-9|6-16|
----------+----+----+---+---+----+
| 1 |Qty |Qty |Qty|Qty|Qty |
----------+----+----+---+---+----+
| 2 |Qty |Qty |Qty|Qty|Qty |
----------+----+----+---+---+----+
| 3 |Qty |Qty |Qty|Qty|Qty |
----------+----+----+---+---+----+

So I've got a massive table with all my products listed as they are
coming due. On the second table, I need the materials for the products
listed above.

One product can have many types of material and one type of material can
have many products. I have over 100 or each product and over 100 of
each material.

Each product has a separate worksheet in the workbook that has
calculations to determine exactly how many pounds of material are needed
for one yard of product.

Each product must have 2-6 types of material.

I need to do this in Excel as I can't get the resources I need to create
a database.

The end goal is to calculate the second table showing what I must
purchase in the way of materials to fulfill orders. Is this possible?
Possible with built-in functions? If not, then how would I approach
building a macro? I have no macro-building experience, but I have
programmed (in an amateur sort of way) in VB6 before.

I'll try anything if I can just beg some help off of you. If you've
read this far, I truly thank you for your time!

-Kevin

I found a solution with a staging Product worksheet, where I aggregate data
from individual sheets A, B, C

I still have problems to find a simple way to extend the number of Products
without changing formulas.

Here is my solution: http://cjoint.com/?fuqqzEPPI6

HTH
--
AP

"Kevin" <[email protected]> a écrit dans le message de news:
[email protected]...
> I'm not faint at heart when it comes to Excel, but I can't figure out how
> to do what I need.
>
> Here it is:
>
> I have a grid structured as follows (Dates on x axis and products on y
> with quantity in yards due listed at the intersections):
>
> +----+-----------------+
> | Due Date |
> +-------+----+----+---+---+----+
> |Product|5-19|5-26|6-2|6-9|6-16|
> --------+----+----+---+---+----+
> | A |Qty |Qty |Qty|Qty|Qty |
> --------+----+----+---+---+----+
> | B |Qty |Qty |Qty|Qty|Qty |
> --------+----+----+---+---+----+
> | C |Qty |Qty |Qty|Qty|Qty |
> --------+----+----+---+---+----+
>
> Now I have this table (Same exact dates on x axis with material required
> on y, with quantity in lbs. required at the intersections):
>
> +----+-----------------+
> | Due Date |
> +---------+----+----+---+---+----+
> |Materials|5-19|5-26|6-2|6-9|6-16|
> ----------+----+----+---+---+----+
> | 1 |Qty |Qty |Qty|Qty|Qty |
> ----------+----+----+---+---+----+
> | 2 |Qty |Qty |Qty|Qty|Qty |
> ----------+----+----+---+---+----+
> | 3 |Qty |Qty |Qty|Qty|Qty |
> ----------+----+----+---+---+----+
>
> So I've got a massive table with all my products listed as they are coming
> due. On the second table, I need the materials for the products listed
> above.
>
> One product can have many types of material and one type of material can
> have many products. I have over 100 or each product and over 100 of each
> material.
>
> Each product has a separate worksheet in the workbook that has
> calculations to determine exactly how many pounds of material are needed
> for one yard of product.
>
> Each product must have 2-6 types of material.
>
> I need to do this in Excel as I can't get the resources I need to create a
> database.
>
> The end goal is to calculate the second table showing what I must purchase
> in the way of materials to fulfill orders. Is this possible? Possible
> with built-in functions? If not, then how would I approach building a
> macro? I have no macro-building experience, but I have programmed (in an
> amateur sort of way) in VB6 before.
>
> I'll try anything if I can just beg some help off of you. If you've read
> this far, I truly thank you for your time!
>
> -Kevin

Here is a new version http://cjoint.com/?furIsCPZHT where you can deal with
255 different materials and 65535 products

HTH
--
AP

Ardus Petus wrote:
> Here is a new version http://cjoint.com/?furIsCPZHT where you can deal with
> 255 different materials and 65535 products
>
> HTH
> --
> AP

Perfect! Thank you for loaning some of your intelligence so I may stay
sane!

I appreciate the help!

Everything works beautifully, except I can't get my offset function to
work with a range on the materials forecast sheet. For the life of me,
I can't figure out how your sheet works and mine doesn't.

Are there any tricks to it?

Kevin wrote:
> Everything works beautifully, except I can't get my offset function to
> work with a range on the materials forecast sheet. For the life of me,
> I can't figure out how your sheet works and mine doesn't.
>
> Are there any tricks to it?

Never mind, I got it. I was starting in row 2 on one and 7 on the
other. It works now and it is WONDERFUL. Thank you again!