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))}

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$65536=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, 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$65536=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
>

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$65536=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