shifting rows 2

[SharonMee]

Hi there:

In my worksheet, the rows keep on shifting, so everytime, I need to change the row # in my formula.

I learnt that dynamic ranges can do the trick; I actually posted something a few weeks back, but I don't understand it still and it's not working for me. I have looked at the couple of the posts on this forum and still don't understand it.

My formulas include columns N, X, T, R & W.

An example of one of the formulas is:
=SUMPRODUCT(('All Data'!$R$2:$R$800="save")*('All Data'!$W$2:$W$800<=20))

so for instance, my new data could have 900 rows, then I have to change the 800 to 900.

Can you tell me exactly, how I could solve my problem with the columns I gave you above. I know that I need to use an offset formula or something like this:
Here is the offset formula I found in: faq68-1331

CODE
=OFFSET(INDIRECT("Sheet1!$A$2"),0,0,CountA(Sheet1!$A:$A)-1,1)

where Sheet1 for me is All Data.
Do I need an offset formula for each of the columns or just for 1 column (column A) and it would take care of the other columns? Row 1 is my heading.

Please please help me.

Thanks.

SharonMee

 

[SkipVought]

Sharon,

You have a formula
[tt]
=SUMPRODUCT(('All Data'!$R$2:$R$800="save")*('All Data'!$W$2:$W$800<=20))
[/tt]
To make it DYNAMIC, you must NAME EACH RANGE using the OFFSET Function.

For instance if the column R range were named rRange and th column W range were named wRange, then
[tt]
=SUMPRODUCT((rRange="save")*(wRange<=20))
[/tt]
CAVEAT: In each of your OFFSET formulas you must use the COLUMN Range CountA(Sheet1!$A:$A) in the example you posted, that contains a VALUE for each row in the range. This COUNT defines the ROW COUNT of the range. then FIRST reference, in the example, INDIRECT("Sheet1!$A$2") defines the BEGINNING of the range, which in your case would be, INDIRECT("'All Data'!$R$2") for rRange and INDIRECT("'All Data'!$W$2") for wRange



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Hi Skip,

I got an REF# when I finished imputing all these formulas.

Here is what I did. My data currently has 59 rows. So for my column N, I highlighted column N2 to N59, Insert-name-define, I put in crrange in the "names in workbook" box and put in the "refers to box":
=OFFSET(INDIRECT("'All Data'!$N$2"),0,0,CountA('All Data'!$A:$A),CountA('All Data'!$1:$1))

I did the same for the other columns, for column X, the range is aprange, T=scrange, R=ddrange, W=allrange

When I put the range in my formula:
=SUMPRODUCT((ddrange<>"save")*(allrange<=20))

I got a REF# error. I noticed that my name ranges do not show in the namebox in the workbook, is this normal?

Do you know what I am missing?

Here are the other offset formula I used for the other ranges:

=OFFSET(INDIRECT("'All Data'!$X$2"),0,0,CountA('All Data'!$A:$A),CountA('All Data'!$1:$1))

=OFFSET(INDIRECT("'All Data'!$T$2"),0,0,CountA('All Data'!$A:$A),CountA('All Data'!$1:$1))

=OFFSET(INDIRECT("'All Data'!$R$2"),0,0,CountA('All Data'!$A:$A),CountA('All Data'!$1:$1))

=OFFSET(INDIRECT("'All Data'!$W$2"),0,0,CountA('All Data'!$A:$A),CountA('All Data'!$1:$1))

Thanks again for helping.

 

[SkipVought]

Sorry that I failed to notice that you are note using the offset function properly

1) to define a SINGLE COLUMN range the WIDTH (very last argument) needs to be 1. The values you are using COUNT the number of COLUMNS of data
[tt]
=OFFSET(INDIRECT("'All Data'!$X$2"),0,0,CountA('All Data'!$A:$A),[red]1[/red])
[/tt]
2) IF you have a heading row, ie VALUES IN ROW 1, then
[tt]
=OFFSET(INDIRECT("'All Data'!$X$2"),0,0,CountA('All Data'!$A:$A)[red]-1[/red],[red]1[/red])
[/tt]
to subtract ONE from the TOTAL ROW COUNT to disregard the Heading Row.

3) The Range Names will NOT appear in the Name Box. I don't understand why -- it's a FEATURE! ;-)

4) Go back to the Defines Names box and verify that your names are defined as intended.

BTW, you can use Name paste [F3] to Select Defined Names in your formulas.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Thanks so much Skip,

This is the first time dynamic ranges are working me.

Thanks again.

SharonMee

 

[xlbo]

what's with the INDIRECT ???

To set up a dynamic range, you do not need INDIRECT

=OFFSET('All Data'!$X$2"),,,CountA('All Data'!$A:$A)-1,1)

will work just as well


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

The INDIRECT NAILS the start range reference and it cannot CHANGE by any spreadsheet operations such as INSERT.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

ahaah - very nice Skip - never thought of that - mind you I would never insert a column before A either but there ya go !!!

Star for that

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

It more to make it "idot proof" if a user INSERTS a row in ROW 2.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244