Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Cell Referencing between worksheets

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi All

    I am trying to get excel to recognise a pattern. The problem is that on sheet 2 of my work book I want cells A1:D1, A8:D8, A16:D18 to reflect identical data from sheet one. The procedure that I am using is:

    On sheet 2 (in cell A1) I am clicking on = then sheet 1, cell A1 then clicking on the green tick. I follow this procedure for cells B1:D1, then again for cells A2:D2 to give excel an idea of the pattern. When I highlight my 2 rows of data in sheet 2 and drag down, I dont get the data I am looking for. I have tried the above procedure on up to 5 lines of data, but still no joy when I drag down.

    Any ideas?

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not use Paste Special? On Sheet 1 select all of the data you want to be copied on to Sheet 2. Copy it to clipboard. Go to Sheet 2 and click the first cell you want - in your case A1. Select Edit, Copy, Paste Special and click Paste Link.

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Will this work if the data on sheet one changes?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Magoo,

    this'll work for that specific example :

    in cell A1, sheet 2 :

    =IF(ROW()=3,INDIRECT("Sheet1!"&CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/1)*7))+1),INDIRECT("Sheet1!"&CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/1)*7))))

    then just fill it accross to column D and then fill down to your 3 destination rows

    is your actual range a lot bigger ? the "pattern" you're referrign to is firstly a gap of 6 rows, followed by a gap of 7 rows

    does it continue...6 then 7 then 6 then 7

    or is it 6 then 7 then 7 all the time ?

    or maybe 6 then 7 then 8 then 9 etc etc etc

    anyhow,
    :: Pharma Z - Family drugstore ::

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 01:12, inarbeth wrote:
    Why not use Paste Special? On Sheet 1 select all of the data you want to be copied on to Sheet 2. Copy it to clipboard. Go to Sheet 2 and click the first cell you want - in your case A1. Select Edit, Copy, Paste Special and click Paste Link.
    Ian,

    That's odd - it seems to increment the links out of sync for some reason. Maybe cos of the gaps being of different sizes ?

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry. Because the ranges are separated they will have to be pasted individually.

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, my example was inacurrate (like my spelling). The gaps are all the same size. I also cant 'cut and paste' because I need my second sheet to link to sheet 1 in order to pick up any changes to the original data (on sheet 1).

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 20:00, Magoo wrote:
    The gaps are all the same size.
    Magoo,

    Contrary to popular belief, we're not pyschic.... are your gaps 6 rows or are they 7 rows ?

    Rant over...

    If your gaps are 6 rows, try this in Sheet2, cell A1 and then copy it to D1 :

    =INDIRECT("Sheet1!"&(CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/7)*1))))

    then highlight A1 to D1 and copy it to your destination cells (CNTRL C then CNTRL V)

    If your gaps are 7 rows,

    try this in Sheet2, cell A1 and then copy it to D1 :

    =INDIRECT("Sheet1!"&(CHAR(COLUMN(BM:BM))&(1+(((ROW()-1)/8)*1))))

    then highlight A1 to D1 and copy it to your destination cells (CNTRL C then CNTRL V)





    _________________
    Hope this helps,
    Chris


    [ This Message was edited by: Chris Davison on 2002-05-07 12:24 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    and were not "psychic" either!

    James

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    *chuckle*

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
  •