Why won't sumproduct notice new information?
 

Hello,

This page has been extremely helpful over the last two weeks, but I can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct, i can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add new
information to the bottom of the database worksheet, it sends all of the
figures to #VALUE. It doesn't seem to want to change the range for each array
to the same number. The last one is always different to the rest and I don't
know how to fix it. so far, everytime I add new information I am having to do
a 'Find and Replace' on all the formulas to update it all. Surely there has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hello,

This page has been extremely helpful over the last two weeks, but I can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct, i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry, I'm
kinda self taught with what I know on Excel so there are little things that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my first
array range, column b in my second array range and column e or f in the third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hello,

This page has been extremely helpful over the last two weeks, but I can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct, i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry,
I'm
kinda self taught with what I know on Excel so there are little things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my first
array range, column b in my second array range and column e or f in the
third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of
the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

Hey, forevertrying, take a look at these two sites:
http://www.exceluser.com/explore/dynname1.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm

Regards,
Ryan--

--
RyGuy


"Don Guillett" wrote:

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry,
I'm
kinda self taught with what I know on Excel so there are little things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my first
array range, column b in my second array range and column e or f in the
third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of
the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

Hi again. Instead of #VALUE I'm now getting #NAME?

Help?

"Don Guillett" wrote:

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry,
I'm
kinda self taught with what I know on Excel so there are little things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my first
array range, column b in my second array range and column e or f in the
third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of
the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

Send your workbook to my address below

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hi again. Instead of #VALUE I'm now getting #NAME?

Help?

"Don Guillett" wrote:

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry,
I'm
kinda self taught with what I know on Excel so there are little things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my
first
array range, column b in my second array range and column e or f in the
third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether
maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the
bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the
offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using
sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of
time.

Unfortunately, in my practice run with it, I have found that when I
add
new
information to the bottom of the database worksheet, it sends all of
the
figures to #VALUE. It doesn't seem to want to change the range for
each
array
to the same number. The last one is always different to the rest and
I
don't
know how to fix it. so far, everytime I add new information I am
having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely
there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

hi ryguy7272,

i looked at them, but they go WAY over my head. I can't make sense of it.
Think maybe Friday frustration is takin gover. I really want it done today,
but I just don't think my brains up to it.

I highlighted the cells that I want to name, then went to insert, name,
define.

With the cells highlighted I entered the formula as Don suggested as follows:

=OFFSET($a$1,1,0,counta($a:$a)-1,1)

it has put the worksheet name in before each '$a'. Is this whats causing a
problem and if so, how do I get it to stop doing it.

I tried to do a little test as suggested on one of those pages you sent me
using 'GoTo'. I typed in the name of my range and it said there wasn't
anything to find!?!

*sigh* wish this bit was as easy as learning sumproduct! lol

"ryguy7272" wrote:

Hey, forevertrying, take a look at these two sites:
http://www.exceluser.com/explore/dynname1.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm

Regards,
Ryan--

--
RyGuy


"Don Guillett" wrote:

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm sorry,
I'm
kinda self taught with what I know on Excel so there are little things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my first
array range, column b in my second array range and column e or f in the
third
array range. I have put that "offset" formula in exactly as you sent it
(largely due to me not really having a clue!) and I wondered whether maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the bottom.
Another is to use a defined name for the range that will make it self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of time.

Unfortunately, in my practice run with it, I have found that when I add
new
information to the bottom of the database worksheet, it sends all of
the
figures to #VALUE. It doesn't seem to want to change the range for each
array
to the same number. The last one is always different to the rest and I
don't
know how to fix it. so far, everytime I add new information I am having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely there
has
to be an easier way?

Thanks

Why won't sumproduct notice new information?
 

You may have misunderstood or I may not have explained fully.
The defined name formula is entered in the RefersTo box and the result is
used in your sumproduct formula

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in message
...
hi ryguy7272,

i looked at them, but they go WAY over my head. I can't make sense of it.
Think maybe Friday frustration is takin gover. I really want it done
today,
but I just don't think my brains up to it.

I highlighted the cells that I want to name, then went to insert, name,
define.

With the cells highlighted I entered the formula as Don suggested as
follows:

=OFFSET($a$1,1,0,counta($a:$a)-1,1)

it has put the worksheet name in before each '$a'. Is this whats causing a
problem and if so, how do I get it to stop doing it.

I tried to do a little test as suggested on one of those pages you sent me
using 'GoTo'. I typed in the name of my range and it said there wasn't
anything to find!?!

*sigh* wish this bit was as easy as learning sumproduct! lol

"ryguy7272" wrote:

Hey, forevertrying, take a look at these two sites:
http://www.exceluser.com/explore/dynname1.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm

Regards,
Ryan--

--
RyGuy


"Don Guillett" wrote:

something like
cola=offset($a$1,1,0,counta($a:$a)-1,1)
colb=offset($b$1,1,0,counta($a:$a)-1,1)
cole=offset($e$1,1,0,counta($a:$a)-1,1)
and use the ranges in your sumproduct formula
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hi Don,

I've tried that formula but I'm not sure I'm doing it right. I'm
sorry,
I'm
kinda self taught with what I know on Excel so there are little
things
that
take me a while to get my head around.

My database runs from a1 to n395. I use column a in sumproduct as my
first
array range, column b in my second array range and column e or f in
the
third
array range. I have put that "offset" formula in exactly as you sent
it
(largely due to me not really having a clue!) and I wondered whether
maybe
I've missed the point with it a bit.

sorry, but thanks

"Don Guillett" wrote:

One way is to INSERT before the bottom instead of adding at the
bottom.
Another is to use a defined name for the range that will make it
self
adjusting such as myrng and the formula
=offset($a$1,1,0,counta($a:$a)-1,10)
To do thisinsertnamedefinename itin the formula type in the
offset
formula.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"forevertrying" wrote in
message
...
Hello,

This page has been extremely helpful over the last two weeks, but
I
can't
find anything relevant to help me out with this one.

I have created a database of information from which, using
sumproduct,
i
can
gather around 54 different figures which saves me an awful lot of
time.

Unfortunately, in my practice run with it, I have found that when
I add
new
information to the bottom of the database worksheet, it sends all
of
the
figures to #VALUE. It doesn't seem to want to change the range for
each
array
to the same number. The last one is always different to the rest
and I
don't
know how to fix it. so far, everytime I add new information I am
having
to
do
a 'Find and Replace' on all the formulas to update it all. Surely
there
has
to be an easier way?

Thanks