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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top