Issues getting Sumproduct working with Indirect

[sirplus]

Hi

A column contains a header which is a named Cell.
I want to return number of Items in the entire worksheet column (the column containing the named Cell).
I'm using the count to create dynamic ranges, it's all automated and needs to be reliable...

CountA is not returning correct answers, it's including some cells with no content.
(unless I Select them first and hit delete, yes they are empty before I delete them, weird -move on),
SUM Product works reliably so this is my workaround, I need this to work.

=SUMPRODUCT(--('Sheet Name'!BJ:BJ<>""))
works

=SUMPRODUCT(--(INDIRECT("BJ:BJ")<>""))
works

=SUMPRODUCT(--(INDIRECT("'Sheet Name'!BJ:BJ")<>""))
works

=SUBSTITUTE(ADDRESS(1,COLUMN('Sheet Name'!Header_aNamedCell),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN('Sheet Name'!Header_aNamedCell),4),"1","")
works
Returns the needed Address Correctly "BJ:BJ"

=SUMPRODUCT(--(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1",""))<>""))
fails
#Value! Error

=SUMPRODUCT(--(INDIRECT("'Sheet Name'!"&SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1",""))<>""))
fails
#Value! Error

I need the last 2 items to work and can't work it out..
All help appreciated, thanks in advance..

 

[Crystalyzer]

Just comparing the formulas that work vs. the last two that fail I see an omission of the 'Sheet Name'! from the last two formulas in the COLUMN function. Could this be the issue?

 

[Tetra201]

Try

=COUNTIF(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")),"<>")

The following will also work, but it is not advisable to use the entire column in a SUMPRODUCT formula:

=SUMPRODUCT(--(INDEX(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(Header_aNamedCell),4),"1","")),)<>""))

 

[sirplus]

Thanks Everyone.

Crystalyzer,
thanks for picking up that irregularity only it was not a problem or a solution

Tetra201
I used your second solution, and it works well.
Is the advice not to refer sumproduct to the whole column to do with reducing CPU load?
If so, in this case the sumproduct formula is only used to create a temporary dynamic names range,
The dynamic named range refersto range is used to create a permanent named range and then deleted.
The permanent Named list is then used with data validation.
This is necessary as data validation lists cannot refer to dynamic ranges..

 

[jasonb75]

This is necessary as data validation lists cannot refer to dynamic ranges..

Not true, the formula below creates a dynamic range that works as a data validation list source without defining it as a named range first.

=OFFSET(aNamedCell,1,,COUNTIF(OFFSET(aNamedCell,,,ROWS(A:A),1),"?*")-1,1)

 

[sirplus]

Sorry for the delay.
Nice if this works.

I'll have a look over the break.