+ Reply to Thread
Results 1 to 8 of 8

Named range - subset

  1. #1
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Named range - subset

    I have a worksheet where my data is stored in columns. Once once column becomes full another one is started, which is not adjacent to the previous one.

    All of the data contained within these multiple columns have been assigned to a single named range, "xData".

    Is it possible to select a subset of consecutive values from this named range? For example the 10th to 20th values.

    Many thanks
    Last edited by jlt199; 08-17-2010 at 11:10 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Named range - subset

    Possibly, Can you be more specific on what you are trying to do? How many rows in each column?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Named range - subset

    Ok, well I'm actually working with the row limit in Excel 2003. But for now, assume that the worksheet has a limit of 20 rows and my range is defined as:

    xRange=Sheet1!$A$2:$A$20,Sheet1!$D$2:$D$20

    Then if I want to select the 10th-30th values in xRange then I would need the last 10 rows in Column A and the first 10 rows in column D.

    I would like to be able to access them dynamically with respect to the range xRange, so I have a cell indicating the first value to select and another cell with the number of values.

    (I'm trying to develop my scroll chart for data exceeding one column)

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Named range - subset

    You can only plot data categories and values that are each in a single row or column.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Named range - subset

    That's not entirely true...

    I currently have a chart where the y values constitute the combination of an initial value, stored in cell "A1", a range of data from a different column and then a final value which is stored in "A2".

    However, in order to achieve this the three disparate sources must first be joined in a single named range, for example:

    =Sheet1!$A$1, OFFSET(Sheet1!$B$1, ChartSheet!$H$8-1, ChartSheet!$K$8-1, ChartSheet!$J$8, 1),Sheet1!$A$2

    So, I can define my named range as required, now all I want to be able to do is refer to a subset of it.

    Many thanks

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Named range - subset

    See attached.
    I used a VLOOKUP table to determine what "Area" the number will be in.
    I used INDEX to return the number with the MOD function determining the position of each number.

    You enter the position of your first number and the # of values you want returned and it creates your range. The positions are automatically generated in Column L and the values in M.
    In M6 for example the equation is

    =IF(ISNUMBER(L6),INDEX(testing, IF(MOD(L6,30)=0,30,MOD(L6,30)),1,VLOOKUP(L6,$I$1:$J$3,2)),"")

    Is this what you are looking for?
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Named range - subset

    Fantastic! I think I can get that to work by adding a hidden worksheet with the values for the chart in.

    Currently I have another scroll bar that moves between the different columns of data, which means that data in different columns can never be shown together. It think this will be a great improvement.

    Many thanks for your time, I may have some more questions in trying to implement this, but looks like it should work.

    I do have one question though, no really related, how did you manage to extend and retract the formatting of columns L and M to the number of data points selected?

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Named range - subset

    I used Conditional Formatting, selecting the range (say L6:M100) and then using a formula =ISNUMBER(L6) and just format the borders. Does that make sense? Glad I could help. Can you mark this thread solved?
    (Go to first post and Edit>Go Advanced> Change Prefix to "Solved")

+ 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