Trouble Designing a Lookup

[Kevin]

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

 

[davesexcel]

well I can sort of see what you are talking about, but I will forward
you to a couple of sites that have vlookup and index explainations

http://theofficeexperts.com/downloads.htm
http://www.mvps.org/dmcritchie/excel/vlookup.htm#No_Frames
http://www.contextures.com/xlFunctions02.html

 

[Ardus Petus]

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

 

[Ardus Petus]

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

HTH

 

[Kevin]

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

HTH

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

I appreciate the help!

 

[Kevin]

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]

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!