dynamic range not working

[RichardO]

Hi I have a sumproduct formula that keeps on shifting because when I ad
new data to the worksheet and then delete the old data, the # of row
in the sumproduct formula shifts.

I tried to use dynamic ranges but the rows are still shifting; probabl
because I am not doing it correctly.

My sumproduct formula is

=SUMPRODUCT((Sheet2!$L$2:$L$999="Amanda")*(Sheet2!$K$2:$K$999="Yes")*(Sheet2!$M$2:$M$999="due")).

Question: apart from inserting the number of rows of my new data in ro
2, would a dynamic range prevent row 999 of the sumproduct formula fro
shifting to say 800 if I add the new data to the last row of th
worksheet and then delete the data above it which is the old data (i.e
the cells shift up)?

Here is what I did to name the dynamic range.

I highlighted column L, insert/name/define
placed "name" in the Names in workbook
placed this formula in refers to:
=OFFSET(INDIRECT(Sheet2!$L$1),0,0,CountA(Sheet2!$L:$L),CountA(Sheet2!$1:$1))

clicked OK.

I did the same for column K & M giving them a different name an
changing L in the formula abbove to K or M. (is there a way to make al
the columns and rows in a worksheet dynamic without having to do eac
one column by column?) I tried that by selecting all the cells in m
worksheet, insert/name/define, put in this formula:
=OFFSET(INDIRECT(Sheet2!$A$1),0,0,CountA(Sheet2!$A:$A),CountA(Sheet2!$1:$1))

But still this didn't work because when I placed the new data in th
last row in my worksheet and then deleted the old data above it, th
sumproduct #row 999 is still shifting. Can someone please tell me wha
I am doing incorrectly?

Thank

 

[Ed Ferrero]

Hi Richard,

You could try changing the sumproduct formula to calculate over the full
column, like this;

=SUMPRODUCT((Sheet2!$L:$L="Amanda")*(Sheet2!$K:$K="Yes")*(Sheet2!$M:$M="due"
)).

That way you do not have to use dynamic ranges.
Otherwise;
There is an explanation of dynamic ranges at
http://edferrero.m6.net/vba.html also you can
find a utility to build dynamic ranges by point-and-click there.

Ed Ferrero

 

[Peo Sjoblom]

Works for me if I understand you correctly, note that you will need to add
one to your dynamic formula if the data starts in
row 2 and you offset from row 1

You can use a single dynamic formula with a single name, it will cost an
extra function,
assume test is the name of the range. I wouldn't recommend it though..


=SUMPRODUCT(--(INDEX(test,,2)="Amanda"),--(INDEX(test,,1)="Yes"),--(INDEX(te
st,,3)="due"))

K L M

so you use index and ,,1 for K, ,,2 for L and ,,3 for M

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

You can't use the whole column/row for array formulas and you'll get a #NUM
error

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ed Ferrero]

Hi Peo,

You're right. Fancy that
=SUMPRODUCT((L1:L65535="Amanda")*(K1:K65535="Yes"))
returns the correct value, whilst
=SUMPRODUCT((L1:L65536="Amanda")*(K1:K65536="Yes"))
returns an error!

You learn something every time!

Ed Ferrero

 

[Don Guillett]

You could use three dynamic range names where all three are tied to the last
row of the 1st.
rngL=offset($l$2,0,0,counta($l:$l,0)
rngM=offset($m$2,0,0,counta($l:$l,0)
rngN=offset($n$2,0,0,counta($l:$l,0)

 

[RichardO]

Hi:

I used Don's dynamic range formula suggested, and I am not getting an
#N/As or #REFs e.t.c. However I noticed that the even though my dat
continues, it ends the range before the end of my data.

I used the following dynamic ranges

name=offset($c$2,0,0,counta($c:$c,0)
due=offset($l$2,0,0,counta($c:$c,0)
yet=offset($m$2,0,0,counta($c:$c,0)
track=offset($n$2,0,0,counta($c:$c,0)

Then I used
=sumproduct=((name="Amanda")*(due="YES")
=sumproduct=((name="Amanda")*(yet="YES")
=sumproduct=((name="Amanda")*(track="YES")

for the last sumproduct formula above, it's not giving me the correc
number, I found out that it's counting only half of the data somewhat.
It works fine for the first 2 sumproduct formula, does anyone know wh
this is the case? the # of rows of data in column C is the same a
that in column N.

Thanks for helping

 

[RichardO]

Hi all:

I just noticed that my offset formula for the dynamic range of "due
(column L), "yet" (column M), "track" (column N) changed to, K,L,& M
they shifted by 1 column to the left when I ran the macro on the page.
The offset formula changed to:
due=offset($k$2,0,0,counta($c:$c,0)
yet=offset($l$2,0,0,counta($c:$c,0)
track=offset($n$2,0,0,counta($c:$c,0)

What the macro does is:
When I paste the raw data, it deletes the last column of that dat
which is in column J, then it adds some formulas to columns J to N, an
then autofills all the rows in the data with this formula. Could th
deletion of column J of the original data be the reason, what can I d
to make the dynamic range work properly, instead of having to redo th
macro.

Thanks again

 

[Aladin Akyurek]

What is the current, exact range of the data of interest? Does the range
crimp/expand vertically? Does the range also crimp/expand horizontally?
Which column that is of numeric type is the most complete?

 

[RichardO]

Thanks Aladin,

The data expands vertically, i.e. # of rows are not fixed. Column
has the most complete data.

The data should not grow horizontally. Here is what I do when I add i
the new data:

first I insert rows to accomodate the new data. Then I Clear All th
old data. The new data has column A to J. the macro then clear
column J, adds headings to J1 to N1 and then adds formulas to row 2 an
autofills the formulas to all the rows of data available. and then th
macro inserts 5 blank rows after a change in column (which holds names
and then counts the number of times in the name shows up.

Someone that the reason the rows and columns are shifting is because
am deleting data, so instead of doing that I am inserting the # of row
I need to accommodate my new data and then clearing the old data.

I need columns C, L, M & N to remain constant. I also need to have th
same number or rows for them starting at row 2 and ending preferably a
row 10000 (to give enough room) and I don't want these rows to chang
no matter what happens to the worksheet.

Is it possible to do this?

Thanks so much