+ Reply to Thread
Results 1 to 13 of 13

is it possible to use a cell reference that refers to the name of a named range

  1. #1
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    is it possible to use a cell reference that refers to the name of a named range

    Hello, I am trying to use a cell reference that refers to the name of a named range in a formula. For example, in the formula "=average(a2,>1)", the cell reference "A2" actually contains the text of the named range "DATA1.1.1_". Is this even possible? If not, is there a way I can make this work? I have a about 800 very long formula's that I need to write and the only thing that is changing in each formula is one named range. I would like be able to set a list of named ranges, then just autofill the formula down the column.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: is it possible to use a cell reference that refers to the name of a named range

    Try this:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    I used an Averageif formula and got a #REF! error. Thanks for the suggestion though!

  4. #4
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: is it possible to use a cell reference that refers to the name of a named range

    I don't see any reason why an AVERAGEIF formula wouldn't work in the same way?
    The only reason it wouldn't work is if you're trying to pull information from another workbook that is closed, as INDIRECT doesn't like that.

  5. #5
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    I figured out why I am getting the #REF! error and fixed it, but the formula is still not returning what I need it to. I have created an example workbook to illustrate what I need to create. Cell Sheet2 A3 uses the actual named range in a COUNT Formula and returns the expected results. The cell Sheet2 B3 uses the indirect formula within a count formula to return the same text as the named range but does not return the correct results.

    Thanks!
    Attached Files Attached Files

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

    Re: is it possible to use a cell reference that refers to the name of a named range

    1.your concatenated name in a1 for example has
    a space in it as there is a trailing space in a2
    so it doesnt = the named range
    also i remember there is a problem using dynamic range names inside indirect
    "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

  7. #7
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    Martin, good eye. I substituted the named range in place of one of the concatenated cells, but it still does not return the correct results. I suspect it may be related to the issue of dynaming ranges inside indirect. I will research that issue.

    Thanks!

  8. #8
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: is it possible to use a cell reference that refers to the name of a named range

    Ok, a couple of points:

    1. When you reference a cell using INDIRECT, you don't put "" marks around it.
    2. You're looking at INDIRECT(B2), but this needs to be INDIRECT(B1)
    3. You have a space after the 1.1.2 in cell B2

    That said, it still didn't work when I made these changes, but for some reason I can't seem to locate where your ranges are. When I re-made some ranges, and changed the formula in B1 to match the new named range, it worked fine. So you may want to check your ranges are functioning properly.

  9. #9
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    If you look on Sheet4 you will see where all of the named ranges resolve to. Essentially every column on Sheet1 is a named range in the format of sheetname&headerrow. The underscore at the end is present because the macro used to create the names replaces any spaces as named ranges cannot contain spaces. From my research, the issue I am facing stems from the inability of the indirect function being able to resolve a dynamic named range. This is why when you created the new non-dynamic named ranges the formula worked. I guess i will have to write each formula out with the dynamic named ranges as they cannot be removed at this point.

  10. #10
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: is it possible to use a cell reference that refers to the name of a named range

    Hmm, that's some hard luck.

    If you have that many to do though, and the formula is exactly the same except the named range, at least it should be pretty easy to write a macro to insert the formulae?

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

    Re: is it possible to use a cell reference that refers to the name of a named range

    do you really need dynamic ranges? whats the biggest range you are likely to get?
    define a name a2:a5000 say then indirect will work

  12. #12
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    Yeah, it sucks. I don't have any idea how to write the macro so I was going to start another thread.

  13. #13
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Re: is it possible to use a cell reference that refers to the name of a named range

    Martin, the dynamic ranges are necessary as the data set grows every month. I would have to set the ranges to to the very limits of excel for it to contain all the data by the end of the lifespan of this sheet. However, in the mean time, to reduce calculation time, I only need to run the calculations on the rows that actually contain data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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