Why won't sumproduct notice new information?

[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

 

[Don Guillett]

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.

 

[forevertrying]

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]

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

 

[ryguy7272]

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

 

[forevertrying]

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

Help?

 

[Don Guillett]

Send your workbook to my address below

 

[forevertrying]

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

 

[Don Guillett]

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