+ Reply to Thread
Results 1 to 2 of 2

named ranged within named range

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    named ranged within named range

    Hello.

    I have a named Range called 'nth_occurrence' where the formula is basically COUNTA($B$8:$B8)

    I have another named range called 'EndRange' where PART of the formula consists of Rows(Pivot!$1:2).

    Now, I want to change the value 2 with 'nth_occurrence' and it's giving me an error.

    Does anyone knows how to change it?

    PS: Even in the formula itself, it's not working

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: named ranged within named range

    not even sure why you want that!
    COUNTA($B$8:$B8) what do you think it does as a named formula?
    at the moment = nth_occurrence in a cel dragged down
    would be the same as COUNTA($B$8:$B8) filled down ie
    =COUNTA($B$8:$B8)
    =COUNTA($B$8:$B9)
    =COUNTA($B$8:$B10)
    =COUNTA($B$8:$B11)
    =COUNTA($B$8:$B12)
    =COUNTA($B$8:$B13)
    =COUNTA($B$8:$B14)
    =COUNTA($B$8:$B15)
    =COUNTA($B$8:$B16)
    =COUNTA($B$8:$B17)


    but
    =ROWS(INDIRECT("1:"&nth_occurrence)) would be the way to construct it
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] named ranged - offset & counta
    By milo1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2014, 02:13 AM
  2. [SOLVED] AVERAGEIFS & Named Ranged Inputted Criteria
    By tskabo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 08:19 PM
  3. sumproduct on named ranged giving #VALUE! error
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2011, 10:02 PM
  4. Lookup Two Named Ranged
    By yawnzzzz in forum Excel General
    Replies: 3
    Last Post: 09-24-2010, 03:10 PM
  5. Replies: 2
    Last Post: 06-01-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1