| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COMBINATION FORMULA
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMBINATION FORMULA
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMBINATION FORMULA
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMBINATION FORMULA
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMBINATION FORMULA
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COMBINATION FORMULA
Well, I'm glad that worked for you.
Thanks so much for letting me know. *********** Regards, Ron XL2002, WinXP "Johnny" wrote: 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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COMBINATION FORMULA | Excel Discussion (Misc queries) | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Excel: Allow combination of formula answer and text in one cell | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Linear Mode
