Dynamic range?!?!

[john434]

Hi all,

I am trying to create a dynamic named range, that changes size as more information is added.

I have read the FAQ 68-1331 posted by skipVought and tried using his worksheet function to achieve this. However, this formula doesn't seem to work. this is the formula as i have typed it:

=OFFSET(INDIRECT("Raw Data!$L$2"),0,0,Counta(Raw Data!$L:$L)-1,1)

The column, i am trying to name contain formula results in text format.

Could this be the reason the offset does not work??

Please help (i think i'm losing my hair).

Cheers

 

[Loomah]

Hi
Don't know why Skip's suggested formula wouldn't work but try this
=OFFSET(Raw Data!$L$1,0,0,COUNTA(Raw Data!$L:$L),1)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[john434]

Thanks for your reply.

For some reason excel adds [data] in the middle of the sheet reference. should i be putting '' around the sheet reference?

Cheers

 

[Loomah]

Sorry for not getting back to you. Wasn't really thinking about it when I posted and just substituted your sheet name for Sheet1!!

In answer to your question, yes there should be single quotes if there are spaces in the sheet name - one reason I've always tried to avoid sheetnames and file names with spaces.

=OFFSET('Raw Data'!$L$1,0,0,COUNTA('Raw Data'!$L:$L),1)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[GlennUK]

Yes, Loomah, I try to avoid spaces in sheet names for the same reason. That's also probably why the INDIRECT method didn't work.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[john434]

Nice One!!!

Thanks for your help Looah, that seems to have sorted it right out!

Just one more thing: (isn't there always)

On another sheet I've got a SUMPRODUCT function that looks at this range and other dynamic ranges.

It reads:
SUMPRODUCT((prog="3 - AMA")*(area="1.2a - Nursing")*(Year="2003/2004))

the three arrays in the above formula are all dynamic ranges and the data is all text (bad form for the year column i know). This formula worked fine when i was manually defining the range, but now they are dynamic i get the #N/A error.

Can i not use subproduct with dynamic ranges or should i be identifying the sheet where the ranges lie within the formula??

Thanks for your help so far dudes.

Jon

 

[Loomah]

Jon
I've had a similar problem in the past but can't remember the solution!! Enter Ken?

One thing that I do know will affect sumproduct (and array formulas) is that the size of the arrays in your formula must be the same

ie
=sumproduct((a1:a10)*(b1*b10)) would work but
=sumproduct((a1:a10)*(b1*b20)) won't!

so that could be the first thing to check

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Amendment to my previous post

ie
=sumproduct((a1:a10)*(b1:b10)) would work but
=sumproduct((a1:a10)*(b1:b20)) won't!

Sorry!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[john434]

cheers loomah,

Unfortunately, i've already checked that and the arrays are the same size.

Any other ideas??

Thanks again for your speedy response!

 

[Loomah]

Jon
Sorry I don't have the answer! However I feel sure this has been covered in this forum (or possibly the VBA forum) sometime in the past and I feel sure I was the one asking the question!!

I've had a quick look through the threads I started and can't find it which might mean it was an incidental question I asked in another thread.

Sorry I can't be more specific!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[john434]

No worries Loom's

Thanks for your help anyway.

Cheers

 

[AnotherHiggins]

The first thing that occurs to me in looking at your formula, john434, is that you are missing a closing quote on the year. Try changing it to:

SUMPRODUCT((prog="3 - AMA")*(area="1.2a - Nursing")*(Year="2003/2004[highlight][attn]"[/attn][/highlight]))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

if th eranges really are the same size, then I think John has nailed why the formula wouldn't work.

That aside, there is nothing wrong with using dynamic ranges, but personally i would ensure that you used the same arguments in the formula for each range to determine the size, eg

=OFFSET($A$4,0,0,COUNTA(A:A))
=OFFSET($C$4,0,0,COUNTA(A:A))
=OFFSET($E$4,0,0,COUNTA(A:A))

Note that there are 3 different ranges, but all use the same COUNT(A:A) to determine the height of the range). This ensures that every time those 3 ranges will definitely be the same size.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[john434]

Thanks for all your help!

It actually turns out that the problem was with the data. In one of the dynamic ranges there were blank fields and fields containg " " (spaces).

This is what was causing the sumproduct function not to work (sorry should have checked this before i posted).

Anyway, the formulas used were:

=OFFSET($L$2,0,0,COUNTA($L:$L))

and

SUMPRODUCT((prog="3 - AMA")*(area="1.2a - Nursing")*(Year="2003/2004"))

RESULT!

to get round the spaces in my column i used the following formula:

=IF(D2=0,"None",IF(D2="","none",IF(D2=" ","None",IF(D2=" ","None",IF(D2=" ","None",IF(D2=" ","None",D2))))))

I'm sure theres a better way to do this, any ideas?

 

[Loomah]

Just out of interest, you could shorten your test formula and make it a little more dynamic by using the following

=IF(OR(A1=0,TRIM(A1)=""),"none",A1)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​