Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relative References horizontal to vertical (2002)

    Hi,

    I have two worksheets. I want some of the information in Sheet1 to be automatically filled in in Sheet2. However, the format of the data is fairly different.
    For instance, in Sheet1, the data in organized in Columns. I.e. Column A contains "Names, ColB contains "Dates", etc. In Sheet2, the data is organized -mostly- in rows (actually it's a grid). Such that in Sheet2!A1 I would like to have "Sheet1!A1 & Sheet1!A2" and in Sheet2!A2, I would like to have "Sheet1!B1 & Sheet1!B2". But the magical "Fill to the Right" doesn't work, regardless of where I put the $ symbol b/c it's expecting the data to be organized the same way. Is there a way around this?

    Thanks,
    -Alex

  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: Relative References horizontal to vertical (2002)

    Relative references are just that relative: copy to the right is a copy to the right...

    I think it could be built with INDEXes and use some calculation for the conversion of Row/column or even a MATCH.

    Can you attach a sample workbook detailing what you need, it is not clear from your description.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative References horizontal to vertical (20

    Ah, ok. Yeah, an example would probably be better to understand. I attach an example. In the first sheet (Data) I have the input data. In the 2nd sheet (BoxLabel_Filled) is where I would like to dump some of the data in a particular format and orientation (in a grid). I filled the first two boxes the way I would like them to end up after a "Fill to the Right" or copy-and-paste magic. i.e. in Sheet2!B3, I reference Data!A3. I now want Sheet2!C3 to read Data!A4, etc...

    Thanks,
    -Alex
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative References horizontal to vertical (20

    Try this horrible formula in B3:
    <code>
    =OFFSET(Data!$A$3,9*ROW()+COLUMN()-29,0)&CHAR(10)&IF(ISBLANK(OFFSET(Data!$F$3,9*ROW() +COLUMN()-29,0)),"",TEXT(OFFSET(Data!$F$3,9*ROW()+COLUMN()-29,0),"MM/DD/YY"))&CHAR(10)&IF(ISBLANK(OFFSET(Data!$N$3,9*ROW() +COLUMN()-29,0)),"","Th:"&TEXT(OFFSET(Data!$N$3,9*ROW()+COLU MN()-29,0),"MM/DD/YY"))&CHAR(10)&IF(ISBLANK(OFFSET(Data!$O$3,9*ROW() +COLUMN()-29,0)),"","Rf:"&TEXT(OFFSET(Data!$O$3,9*ROW()+COLU MN()-29,0),"MM/DD/YY"))
    </code>
    (Note: this is one formula, even though the browser splits it over several lines)
    You can fill down then right, or right then down.

  5. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative References horizontal to vertical (20

    Hi Hanz,

    Good Lord! Wow. What a formula! :-o

    It works!

    And now I state with some trepidation... I am going to try to figure out what you did with offset and try to fill my other rows of the grid (B through I)!!

    In any case, you've already helped a lot! Still can't believe the size of that cell. Excel is really trying to tell me I should be using a bona-fine db program... :-)
    Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative References horizontal to vertical (20

    Once you enter the formula in cell B3, you can fill right to cell J3.
    With B3:J3 still selected, fill down to B11:J11.

    Or first fill down from B3 to B11.
    With B3:B11 still selected, fill right to J3:J11.

    The essential part of the formula is

    =OFFSET(Data!$A$3,9*ROW()+COLUMN()-29,0)

    The rest consists mostly of concatenation and duplication.

    If you go one cell to the right in the grid, you want to go one cell down in the data sheet.
    If you go one cell down in the grid, you want to go nine cells (the width of the grid) down in the data sheet.
    So the offset is 9 * (row of cell - row of B3) + (column of cell - column of B3) = 9 * (ROW() - 3) + (COLUMN() - 2) = 9*ROW() + COLUMN() - 29

  7. #7
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative References horizontal to vertical (20

    You dear Sir, are a genius. :-)

    Totally awesome.

    THANKS!
    -Alex

Posting Permissions

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