You are amazing. I wish I had access to this avenue of resources years ago.
Thank very much for your time & help. I wish that I had time to sit down and
learn all these formulas inside and out. Well all and all you made my day.

Thanks again,

Johnny




"Ron Coderre" wrote:

Try this:

With Worksheet 2
A2: (number to lookup)
G1: (date to lookup)

AND...this structure in Worksheet 1:
Dates in A1, D1, G1
A2: Number
B2: Description
C2: Quantity
D2: Number
E2: Description
F2: Quantity

Then
On Worksheet 2, the QTY to be calculated is
(Non-Volatile formula)
D2: =VLOOKUP(A2,INDEX('Worksheet 1'!$3:$3,1,MATCH($G$1,'Worksheet
1'!$1:$1,0)):INDEX('Worksheet 1'!$6000:$6000,1,MATCH($G$1,'Worksheet
1'!$1:$1,0)+2),3,0)

(Volatile formula)
D2: =VLOOKUP(A2,OFFSET('Worksheet 1'!$A$1:$D$6000,0,MATCH($G$1,'Worksheet
1'!$1:$1,0)-1),3,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Johnny" wrote:

Ok Ron,

I haven't been able to get this formula off of the ground but I see one flaw
that might court against me. The DATE will have to be MATCHED within a
horizontal ARRAY. If you can recieve e-mail i can zap over the file so that
you can check it out. If not I will attemt to past as much as i can.

WORKSHEET 2:
A B C G
__________________________________________________ _______________
Number Description QTY 9/15/2006

297102 PLATE (5X4) 60
__________________________________________________ _______________

note: (D2) which equal (60) is where i want to put the (VLOOKUP)formula

WORKSHEET 1:
A B C D E
F G
__________________________________________________ _______________
9/8/2006 9/15/2006
9/22/2006
Number Description Quantity Number Description Quantity
-----
297102 PLATE (5X4) 162 297102 PLATE (5X4) 162
-----
300485 CONV F-500 2 494003 BK HLMT 55129
-----
490806 CL242 BULK 73225 297102 PLATE (5X4) 60
-----
490808 CL452 BULK 10309 490806 CL242 BULK 73225 -----

note: this worksheet is added to (WKLY)



In WORKSHEET 1 cell (A2) the formula that is currently there is:

=VLOOKUP(A2,'WORKSHEET 1!D$3:F$6000,3,0)

The formula that I need has to be able to shift the (TABLE_ARRAY) when the
DATE on WORKSHEET 2 is maually updated to reflect the week I am looking for
on WORKSHEET 1.

A new set of columbs are added to WORKSHEET 1 every week. It is almost like
I want to combine a VLOOKUP after an HLOOKUP.









"Ron Coderre" wrote:

Try something like this:

With
A2: Number
A3: (a number, eg: 297102 )

B1: (a date)
B2: QTY

This is a non-volatile* formula
B3: =VLOOKUP(A3,INDEX('Worksheet 1'!$3:$3,1,MATCH($B$1,'Worksheet
1'!$1:$1,0)):INDEX('Worksheet 1'!$10:$10,1,MATCH($B$1,'Worksheet
1'!$1:$1,0)+1),2,0)

This formula is volatile*
B3: =VLOOKUP(A3,OFFSET('Worksheet 1'!$A$1:$B$10,0,MATCH($B$1,'Worksheet
1'!$1:$1,0)-1),2,0)

Note_1: "Volatile" means the formula recalculates whenever the workbook
recalculates, instead of only when its referenced cells change.

Note_2: In case text wrap impacts the display, there are NO spaces in those
formulas, EXCEPT in the worksheet reference.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Johnny" wrote:

Is there a possible way LINK a (formula table array) to a cell, that when
updated it shifts that formulated array to corresponding coordinates?

WORKSHEET 1: columns A,B,C,D

9/8/2006 9/15/2006
Item Item Item Item
Number Quantity Number Quantity
300482 162 300482 162
300485 2 300485 2
490806 73225 490806 72935
490808 10309 490808 10277
490810 109835 490810 109467
490815 32042 490815 31942
494003 55129 494003 50523
496900 1785 496900 1381

WORKSHEET 2: just the FORMULA

Number QTY
297102 =VLOOKUP(A2,'WORKSHEET 1'!A$3:A$10,3,0)

LINK: with in this formula I would like to incorporate a LINK the DATE from
(WORKSHEET 1) so if I change the date within a cell on (WORKSHEET 2) to read
(9/15/06) the formula will automatically change the array from (A$3:A$10) to
(C$3:D$10)