Selecting a dynamic range

    Hello,

    I have a workbook with a large amount of data. The workbook is frequently updated with more data and constantly grows. Another workbook is used to trend this data, and currently uses a static range of 1000 columns from the first entry.

    The problem is that when the data eventually reaches the 1000 column limit then the trending workbook won't keep track. Also it is quite likely that 1000 data points will be too much and the trend charts will lose their value by being overloaded.

    I need a way to have a dynamic range of about 400 columns to start with...may be reduced if that's too many. The range would end at the last data point and automatically move as new data is entered.


    I was thinking a way to do this would involve copying the dynamic range so that it is displayed below the original data, near the start of the data series. Those cells can then be referenced by the trending workbook. I'm not sure what the best way would be to get excel to create a dynamic range based on the rightmost point of data though.

    Is anyone able to help me? I would prefer to use something that is formula based, but if that's not possible then I can try VBA.

    Re: Selecting a dynamic range


    Hi Xenoun,
    Nice to meet you.
    I use data > list followed by name > Create.
    If you use this, named range is auto adjusted.
    Regards, junho

    Re: Selecting a dynamic range


    Thanks for the reply. I'm having trouble seeing how this works though. I've previously looked at a named range using the offset formula but my problem is in getting the range of 400 columns to move right as more data is added that goes outside of the previous range.

    My dynamic range isn't one that expands/shrinks depending on the data, but rather has a static size and moves with new data

    Re: Selecting a dynamic range


    Quote

    my problem is in getting the range of 400 columns to move right as more data is added that goes outside of the previous range.


    Here are a couple of variations that shift right as new data are added.


    =OFFSET(INDIRECT("Sheet1!"&ADDRESS(1,COUNTA(Sheet1!$1:$1))),0,0,1,-400)
    =OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$1:$1)-1,1,-400)


    Of course, adjust the arguments per your needs.


    Each of these formulas will determine the last cell on, say row-1, and offset backwards from there.
    You didn't say how many rows are included or if they are static or changing. If you need dynamic rows, then replace the static value with: COUNTA($A:$A)

    Re: Selecting a dynamic range


    I misread before, the rows are 12:49 so there are 38

    I've copied your second formula and entered it as an array, then dragged it out and it shows a copy of the original data like I want.

    However I don't think i've done it right as I don't see how this will adjust to new sets of data. Would you be able to outline the steps of how to use that? If I need to highlight the original data and make a named range or highlight a 38 row by 400 column area and make that a named range with the formula? I'm not entirely sure how this thing works

    Re: Selecting a dynamic range


    For some reason it also seems to have counted along 253 columns before showing the first data entry so i'm missing 253 lots of data


    Edit: Just realised what's happening...the first column of data has extra entries in it that someone else put in for formatting purposes. The offset is finding the rightmost and counting back 400 columns. I can work around that, but would you be able to tell me if this would automatically update when new data is updated or if i'd need to refresh it?

    Re: Selecting a dynamic range


    Quote

    I've copied your second formula and entered it as an array, then dragged it out and it shows a copy of the original data like I want.



    Neither of the formulas are array formula. They are intended to be used in the Name Manager to refer to a range of cells defined by the formula. The formula should not be enterted into a cell.

    Suggest you read this page on Dynamic Ranges to understand them better.

    Quote

    The offset is finding the rightmost and counting back 400 columns. I can work around that, but would you be able to tell me if this would automatically update when new data is updated or if i'd need to refresh it?



    Yes, it is finding the right-most column and including the previous 400 columns as this is what you indicated you need. Because it is a dynamic named range it wll continually shift to the right as new columns are added, which means an equal number of previous columns will be excluded relative to the number of new columns added.

    Re: Selecting a dynamic range


    Ok, i've entered it as a named range and it appears to be working. However, I need to be able to reference that named range to another workbook.

    Previously I was just linking a static range of cells in the other workbook and it would update whenever I opened the workbook. Is there a way for me to do something similar with the named range? Either if I can get excel to display what is in the named range so I can link those cells or if there is a way for me to directly link the named range for my other workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login