Excel formula: Count number of non blank cells in column next to named range

[JackDanIce]

Hi,

I have a named range: "Campaign" in N2 with capaign names in O4:O10

I also have a named range: "Campaign_List" =OFFSET(Campaign,2,1,COUNTA(Settings!$O:$O)-1)

How can I replace Settings!$O:$O to something relative to Campaign? This is to be used for Data validation as a list formula.

I then wish to create the same _List name for another 2 column table where Cells(2, x) is a named range, so in effect:
"Count number of non blank cells in column 1 right of named range"

TIA,
Jack

 

[James006]

Hi,

An idea among others ...

Adding a new Range named OffCampaign ... defined by =OFFSET(Campaign,0,1)

Then you Counta() can be applied ...

Hope this will help

 

[JackDanIce]

Hi James,

Unless I misunderstood, I tried this: =OFFSET(Campaign,3,1,COUNTA(OFFSET(Campaign,0,1))-1) which is only address $O$5, whereas I need it to be $O$5:$O$7 because there are only 4 values in column O (4-1 = 3 cells from O5)

 

[James006]

Hi Jack,

Sorry if I was not clear enough ...

=OFFSET(Campaign,3,1,COUNTA(OffCampaign)-1)

With a newly created Named Range OffCampaign

and a Refers To : =OFFSET(Campaign,0,1)

HTH

 

[JackDanIce]

No you were clear, thank you, I was trying to avoid creating another named range and substituting the address of that instead, feel it should be possible of that first named cell since the two columns are always going to be relative to it as Cells(1,1)

I'll try your suggestion anyway!