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

1. ## Excel (Offset?) Reference Formula in Referenced Cell

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  Reply With Quote

2. 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),"")  Reply With Quote

3. That is indeed it! Thank you, kind sir ~  Reply With Quote

excel reference offset 