Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display dynamic result range (2002/Any)

    Is there a formula to look at a range of cells "hidden" to the user at the far right side of a spreadsheet and display in column A? At the most basic level, if the data is stored in Z1 and I want to display in A1, I would put the following in A1: =Z1 But the hidden data shrinks and expands in terms of rows, and I'd like to create just one formula if possible. Can this be done? Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Display dynamic result range (2002/Any)

    You can create a DYNAMIC range name using OFFSET
    eg the refers to can be:
    =OFFSET(reference,rows, cols,height,width)

    Each of the variables could be static to define a constant column or width or based on a cell or range name.

    I can't give you a specific formula unless you give more details of how you will define this "hidden range"

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display dynamic result range (2002/Any)

    I cannot get the OFFSET function to do exactly what I want. In simplest form I am trying to do this. Let's say the values Name, 1, and 2 are sitting in cells C1, C2, and C3, respectively. I want those cells to populate in cells A1-A3 with a single formula in cell A1. Is using OFFSET my best option?

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display dynamic result range (2002/Any)

    The only way I know of populating a range of cells in to copy the cell in A1 and paste it down the column. This can also be done dynamically with a macro. After the macro is run each cell in col A will have it's own formula which references ( a cell in col C or Z for instance). But to put one formula in cell A1 that populates A2:A5 would be new to me.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display dynamic result range (2002/Any)

    A formula can not change a cell other than the cell it is in. You can use an array formula to sort of do what you are asking:

    1- Select cells A1:A3 with A1 being the active cell.

    2- Enter this formula:

    =C1:C3

    Hold down the Ctrl and Shift keys when you press enter.

    That will put an array formula that will put put C1:C3 into A1:A3. You only entered the formula into A1, but technically the formula is in A2 and A3 and will display in the formula bar if you select any of the cells A1:A3.

    If we knew what you are trying to accomplish, there might be some other way.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Display dynamic result range (2002/Any)

    (Edited by HansV to correct <!t>[pre]<!/t> tags)

    I am still unclear what your "goal " is. A couple of thoughts:

    Technically, if you enter the formula into A1:<pre>=C1&CHAR(10)&C2&CHAR(10)&C3</pre>

    Select A1:a3
    Choose format cells - alignment tab
    and check "Wrap text" and "Merge cells"
    The results are ONLY in A1 (A2 and A3 are blank), though they will LOOK like they are in A1:A3

    Another option:
    You could also use the camera tool
    [To add camera tool to toolbar (it is NOT there by default):
    View -Toolbars -customize
    commands tab - Tools category
    The command "Camera" is near the bottom:
    "drag" into onto one of the toolbars. then <close>]

    Once the camera tool is available:
    highlight C1:c3, click on the camera tool [C1:C3 will be selected, the cursur will be a "+"]
    Put the "+" where you want the upper left corner of the "picture" and drag the size of the shape (it does not matter what size you drag it will be a copy of the size of the the cells you select] you can move this LIVE picture. ANy changes to C1 and C3 will be reflected in this picture (formatting, anything]

    If you select the picture you will notice in the formula bar it is labeled:
    <pre>=$C$1:$C$3</pre>

    You can change this to include the sheetname or even just change it to a RANGE name. [note if you select the camera tool and when it is the "+" cursor, you GO to a different sheet, the sheetname will automatically be attached].

    Including the sheetname or using a range name allows the PICTURE to be linked to cells on a different sheet than the "picture".

    The range name can be a dynamic range name (created with OFFSET) so if you sometime want C1:C5, but when the list has (for example) "#N/As" ror some other indicator in C4 and C5 you only want C1:C3, or ONLY C1 in another instance, the Dynamic formula with OFFSET will automatically fix this

    <post#=257079>post 257079</post#> has a couple of sites which talk about using dynamic links.

    As another aside, this "picture" can be added to chart sheets allowing a "DataTable" of values to be added which can be formatted and can also change as your data changes (even the size can change, dynamically).

    Steve

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Display dynamic result range (2002/Any)

    You said, "the hidden data shrinks and expands in terms of rows"

    Hmmmm... <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Does that mean you want your formula "=Z1" to stay "Z1" even if you copy it all the way down Column A?
    If so, just use "=$Z$1" Dollar signs "lock in" the column and row refs when you copy the formula.

    I may have completely missed the point of what you are trying to do, but my gut tells me it can't be as complicated as it sounds.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Display dynamic result range (2002/Any)

    If you post here with an attached Excel file showing a simplified example of what you want, that would be helpful.
    Using a VLOOKUP formula in A might be the answer if your "hidden data" is a lookup table. Instead of referring to a specific cell in Z you'd be "looking up" the right cell in the hidden lookup table by finding the appropriate match by date or customer number or income level or whatever...
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

Posting Permissions

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