Why won't sumproduct notice new information?

  • Thread starter Thread starter forevertrying
  • Start date Start date
F

forevertrying

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
 
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 this>insert>name>define>name it>in the formula type in the offset
formula.
 
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
 
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
 
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
 
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
 
Back
Top