| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Formula into Array
Thanks for looking at this. I have checked through the help (F1) but somehow
or rather, just can't find the solution: What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1,DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
" wrote...
.... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Harlan,
It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Correction;
From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
In other words, you want to use a dynamic range. You would have to do the
same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Valko,
Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| inserting hard CR in a formula | Excel Worksheet Functions | |||
| Formula for inserting blank row | Excel Worksheet Functions | |||
| mantaining formula when inserting col | Excel Discussion (Misc queries) | |||
| inserting a formula | Excel Discussion (Misc queries) | |||
| Formula changes while inserting a row !!!! | Excel Worksheet Functions | |||
Hybrid Mode
