Counta(Offset...)) works in cell but not named formula; cell references are not relative.

**January1** · 12-10-2014, 03:07 PM

Hi everyone,

I have created many dynamic named ranges in the past, but this one is giving me trouble.

I have a list that begins in cell A4 for now. The rows above are reserved for the subtotal(109,range) function to be used with autofilter below. Column A:A is the column to calculate range height via the Counta function. There are currently some blank cells above the list which may or may not get filled in by my boss. She may also want to insert extra rows above for additional computations. So I am attempting to subtract from Counta($A$A) any non-blank cells in that column above the list. This formula gives the correct count when I place it in a cell, but returns "1" when I assign it to a named formula: =COUNTA(OFFSET(Stores!$A$4,-(ROW(Stores!$A$4)-1),0,ROW(Stores!$A$4)-1,1)). Locking cells and protecting the worksheet is a last resort.

I am using Excel 2013 on machine running Windows 7

Thank you for any help you can provide!

**karedog** · 12-11-2014, 04:20 AM

Hi,

If you use your above formula in name range object, the argument of counta() function is not evaluated yet as a range object, so :

Please Login or Register  to view this content.

is equal to :

Please Login or Register  to view this content.

Both will yield 1 (the number of counta() arguments)

Another example :

Please Login or Register  to view this content.

is equal to :

Please Login or Register  to view this content.

Both will yield 3 (the number of counta() arguments)

You should use indirect assignment like this :

- First, create a name range object, let's say it is Range1

Please Login or Register  to view this content.

- Then, because this Range1 has already evaluated, you can use it as argument for counta() in a new name range object. Let's say it is RangeA

Please Login or Register  to view this content.

Now, when you call this name (=RangeA), it should show the expected result.

Regards

**January1** · 12-12-2014, 02:12 PM

Thank you Karedog; you solved it! And thanks for the explanation, examples, (and reminder re posting code).

When creating named formulas, I will take note when a formula expects a value versus a range and plan accordingly. (I need to learn how Excel calculates Named Formulas differently from formulas in cells!)

Thank you again.

**karedog** · 12-12-2014, 02:42 PM

You are welcome January1 and many thanks for the reputation point.

Regards

Counta(Offset...)) works in cell but not named formula; cell references are not relative.

LinkBack

Thread Tools

Rate This Thread

Display

Counta(Offset...)) works in cell but not named formula; cell references are not relative.

Re: Counta(Offset...)) works in cell but not named formula; cell references are not relati

Re: Counta(Offset...)) works in cell but not named formula; cell references are not relati

Re: Counta(Offset...)) works in cell but not named formula; cell references are not relati

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Offset Function works in cell, not in named range

[SOLVED] Offset Function works in cell, not in named range

Offset Function works in cell, not in named range

Offset Function works in cell, not in named range

Offset Function works in cell, not in named range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions