Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in array formulas
I have a .csv file that I import into a sheet. Another sheet includes array
formulas that get the data for each store. One week the rows of data my encompass A2:A538, the next week the range might be A2:A1040. An example of one of the formulas follows; my formula ranges don't seem to adjust if the range gets longer. I'm not sure this makes sense or not, but the $538 should be $1040. Does the formula work as it should and the range does not change, and if so how do I get around this problem? Thanks as always for any help with this. {=SUM(IF(A!$C$2:$C$538=PAC!$A7,IF(A!$A$2:$A$538=10 ,A!$B$2:$B$538,0),0))} |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in array formulas
You can't use the whole column, but you could use everything except for row 1:
{=SUM(IF(A!$C$2:$C$65536=PAC!$A7,IF(A!$A$2:$A$6553 6=10,A!$B$2:$B$65536,0),0))} (Or some number of rows that will never be exceeded. (10000???).) cottage6 wrote: I have a .csv file that I import into a sheet. Another sheet includes array formulas that get the data for each store. One week the rows of data my encompass A2:A538, the next week the range might be A2:A1040. An example of one of the formulas follows; my formula ranges don't seem to adjust if the range gets longer. I'm not sure this makes sense or not, but the $538 should be $1040. Does the formula work as it should and the range does not change, and if so how do I get around this problem? Thanks as always for any help with this. {=SUM(IF(A!$C$2:$C$538=PAC!$A7,IF(A!$A$2:$A$538=10 ,A!$B$2:$B$538,0),0))} -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in array formulas
Dave, thanks for the suggestion. However, even if I change to $65536 the
next time I import the .csv file the range changes to the last used cell such as $1008. That's fine but I have a problem if the next file I import exceeds row 1008; the $1008 doesn't adjust to the larger range. Any ideas? If need be I can set up some offset ranges which should work, but I wondered if there was a better way. "Dave Peterson" wrote: You can't use the whole column, but you could use everything except for row 1: {=SUM(IF(A!$C$2:$C$65536=PAC!$A7,IF(A!$A$2:$A$6553 6=10,A!$B$2:$B$65536,0),0))} (Or some number of rows that will never be exceeded. (10000???).) cottage6 wrote: I have a .csv file that I import into a sheet. Another sheet includes array formulas that get the data for each store. One week the rows of data my encompass A2:A538, the next week the range might be A2:A1040. An example of one of the formulas follows; my formula ranges don't seem to adjust if the range gets longer. I'm not sure this makes sense or not, but the $538 should be $1040. Does the formula work as it should and the range does not change, and if so how do I get around this problem? Thanks as always for any help with this. {=SUM(IF(A!$C$2:$C$538=PAC!$A7,IF(A!$A$2:$A$538=10 ,A!$B$2:$B$538,0),0))} -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in array formulas
How does that formula get changed? I don't see what would change it...
But how about just using a macro to repopulate the formula whenever you import the new data? cottage6 wrote: Dave, thanks for the suggestion. However, even if I change to $65536 the next time I import the .csv file the range changes to the last used cell such as $1008. That's fine but I have a problem if the next file I import exceeds row 1008; the $1008 doesn't adjust to the larger range. Any ideas? If need be I can set up some offset ranges which should work, but I wondered if there was a better way. "Dave Peterson" wrote: You can't use the whole column, but you could use everything except for row 1: {=SUM(IF(A!$C$2:$C$65536=PAC!$A7,IF(A!$A$2:$A$6553 6=10,A!$B$2:$B$65536,0),0))} (Or some number of rows that will never be exceeded. (10000???).) cottage6 wrote: I have a .csv file that I import into a sheet. Another sheet includes array formulas that get the data for each store. One week the rows of data my encompass A2:A538, the next week the range might be A2:A1040. An example of one of the formulas follows; my formula ranges don't seem to adjust if the range gets longer. I'm not sure this makes sense or not, but the $538 should be $1040. Does the formula work as it should and the range does not change, and if so how do I get around this problem? Thanks as always for any help with this. {=SUM(IF(A!$C$2:$C$538=PAC!$A7,IF(A!$A$2:$A$538=10 ,A!$B$2:$B$538,0),0))} -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Help with Loop / Array / Ranges | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming | |||
Array of page ranges | Excel Programming |