Results 1 to 3 of 3

Thread: Excel (Offset?) Reference Formula in Referenced Cell

  1. #1

    Excel (Offset?) Reference Formula in Referenced Cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Friends in Excel,

    Having trouble finding an answer to this simple problem online; thank you for offering help. (Excel for Mac 2011 Version 14.5.1)

    Sheet1 has a list of part numbers (in a column) that I update regularly. Data:
    A1: 2732803
    A2: 2732804
    A3: 2732806
    As you can see, each part number is directly below the last one.

    Sheet2 monitors incoming and outgoing movement of these part numbers. I would like to reference the part numbers on Sheet 1, but skip two rows between each part number (where I calculate the in/out activity). Final results should read:
    B2: 2732803 (referencing cell A1 on Sheet 1)
    B3: Incoming (text)
    B4: Outgoing (text)
    B5: 2732804 (referencing the next cell down in the A column on Sheet 1, i.e. A2)
    B6: Incoming (text)
    B7: Outgoing (text)
    B8: 2732806 (referencing the next cell down (A3), etc.)
    B9: Incoming (text)
    B10: Outgoing (text)

    In Sheet2 I need formulas in cells B2, B5 and B8. B2 is at the top of the list on Sheet2 and will pull the first part number on Sheet1 simply using the formula "=Sheet1!A1", calling up the value "2732803". No problem here.
    But I'd like the formula in B5 (three cells down from B2) to see the formula in B2 and grab data from Sheet1 onecell down, producing the value "2732804". Similarly the formula in B8 (three cells down) should see the formula in B5 and grab data from Sheet1 one cell down, producing the value "2732806".

    I think the Offset formula might be involved but am not sure. Thank you for any help - xiaobo

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    In cell B2 of sheet 2, enter the following formula and copy down
    =IF(MOD(ROW(),3)=2,INDEX(Sheet1!A:A,INT(ROW()/3)+1),"")
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    That is indeed it! Thank you, kind sir ~

Tags for this Thread

Posting Permissions

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