Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Link to cells plus one row down

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Toronto
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have data on one sheet in row 1 through 5. I would like to link to this data in 5 separate rows on another sheet. The other sheet where I would like to set up the link from, the data is not in consecutive rows. i.e. row 1, 6, 12, 16 etc. When I copy and paste the reference link it automically assumes that I want to pull data 5 rows down (row 1 to row 6). Is there are way to reference a cell above plus one row down.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know what you mean. Can you explain it in a different way?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Toronto
    Posts
    275
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Information is stored on one page in cells a1:a5.

    I want to link to this information on page 2 in cells, a5, A8, A11 etc.

    When you link page 2 cell a5 to page 1 cell a1, it doesn't copy down correctly. - copy down to cell A8 and A11 etc. I would like to link non successive cells to successive data. Is this possible.?

    Thanks,

    Jamie

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    1. Enter the formula...

    ="=Sheet1!A"&ROUND(TREND({5,8},{1,2},ROW()),0)

    ...into cell A1 of your 2nd sheet and fill down as far as needed.
    2. Perform a Copy/Paste Special... Values to replace these formulas with their resultant values.
    3. Choose the Edit | Replace... menu command and find = and replace with =.

    You can adjust the starting row number and increment by changing the 1st TREND function argument. For example, {8,17} would start at Shee1!A8 and increment by 9. The 2nd TREND function argument should be the first two rows where this formula will be entered on Sheet2. For example, if you want to begin in Sheet2!A5 use {5,6}.

    [ This Message was edited by: Mark W. on 2002-04-18 12:34 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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