Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Reference Formulas for Data on Other Worksheets

    I have aworkbook with two worksheets. I have alot of formulas on the second worksheet that references Worksheet 1 (as an examplethe formula in cell A1 on worksheet2 is =worksheet1!B1). The next formula on worksheet2 (cell A2), goesdown 7 rows on worksheet1 and references that cell (i.e., =worksheet1!B8). I will have hundreds of these a formula thatwould look down 7 cells on worksheet1 from the previous formula. Is there a formula that will do this without havingto manually enter the formula for each cell? Iím not sure I have done an adequate job explaining my intentions, beloware a couple of formulas.

    Worksheet2

    A1 formula is =worksheet1!B1
    A2 formula is =worksheet1!B8
    A3 formula is =worksheet1!B15
    A4 formula is =worksheet1!B22
    A5 formula is =worksheet1!B29


    I know there has got to be an easier wayto enter these formulas.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    See this link on how to set up indirect links to another worksheet. Note that the other worksheet must be open or the =INDIRECT() function returns #REF errors.

    Please post back if you need more help getting the cell references set up.
    PJ in FL

  3. #3
    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
    In A1:
    =OFFSET(worksheet1!$B$1,7*(ROW()-1),0)

    Copy A1 and paste to A2:A5...

    Steve

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    sdckapr,


    Sorry I've not gotten back earlier, I've been out of town.I wanted to say thanks. This is exactly what I'm looking for. However, I dohave any additional question. I need to insert 17 additional rows into myworksheet, so the first cell on worksheet1 is no longer B1, but rather B18. Ithen pick up as I previously indicated (i.e., look down 7cells on worksheet1 from B18). I tried just changing the formula you providefrom $B$1 to $B$18 (=OFFSET(worksheet1!$B$18,7*(ROW()-1),0),but I did not get the same results before I entered the additional 18 rows. Anysuggestions? Again, thanks for your help.


  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want a simple way, you can do enter this:
    A1: |worksheet1!B1
    A2: |worksheet1!B8

    now select A1:A2 and drag down using the fill handle as far as required. Then select the cells, ctrl+H and enter "|" (without the quotes) in the Find box and "=" in the Replace box (again, without quotes) and press Replace All.
    Regards,
    Rory

    Microsoft MVP - Excel

  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
    If I have in A1:
    =OFFSET(worksheet1!$B$18,7*(ROW()-1),0)

    it seems to work as desired. It gives B18, B25, B32, B39, etc...

    Did you change the setup in Worksheet 2 as well?
    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    sbdale (2011-05-11)

  8. #7
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    sdckaper,

    I did change Worksheet 2. That was my problem, once firgured that out it was a simply fix. Again, thanks for your help!

Posting Permissions

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