Dynamic named range offset problem

[owentmoore]

Hi

I have a dynamic named range as follows:

 = OFFSET('Summary Data'!$Z$2,0,0,COUNTA('Summary Data'!$Z$2:$Z$2),COUNTA('Summary Data'!$2:$2))

The code picks up creats the range from Z2 as required, counts along row 2 as required, but instead of stopping at my last value, keeps counting 20 columns past my last column containing a value.

When I put this exact code in a different sheet, substituting the sheet name as appropriate, it counts the correct number of columns!

I cannot figure out why it is doing this.

Does anyone have any suggestions????

Thanks
Owen

 

[AnotherHiggins]

First, I question why you would use this:

COUNTA('Summary Data'!$Z$2:$Z$2)

You are counting how many populated cells there are in a range. The range consists of only one cell!?!? Did you mean to say COUNTA('Summary Data'!$Z:$Z) ?

Now to your actual question:

If COUNTA('Summary Data'!$2:$2) is returning 20 values more than you expect, I suspect that you have some formulas on row 2 that are returning a zero-length string (something like "=if(a1="","",1)" ). Those formulas will be counted as populated cells. If that isn't the case, then you might have some cells that look blank but actually contain spaces.

Try deleting all columns to the right of your data and see if that fixes the problem.

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

 

[owentmoore]

Thanks for the reply John.

I suspected also that I may have had extra "hidden" data in these extra fields and selected all fields after the row in question and deleted them. This did not fix the problem.

I've experimented with deleting all other entries on the sheet except for the text I need to select (which is in the range Z2:BE2). then when I test to see if the dynamic named range works - it does!! I'm positive I have all the text after cell BE2 deleted.

Owen