Could someone assist me with the correct syntax to perform the following:

I want to define a named ranged starting at cell B2 that expands down to the last entry based on column A. Column A has no empty cells and B does and the values are discontinuous.

In other words, create a dynamic range based on col B minus the first cell (B1) that includes as many remaining cells that are full in col A.

My attempts continue to add one extra cell at the very end of the range.

ie. OFFSET($B$2,0,0,COUNTA($A:$A),1)

Thx

Hi Cerengine.

I notice in your formula that you are counting the entire of column, by any chance does this also have a value in the first row that you need to ignore? I.e. do you have column headings.

Then it would just be:

OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)



Stu.

That did it. I would swear I tried that, but its hard to see the forest through the trees sometimes.

Thanks Stu

Glad to help.