Excel dragging formula not incrementing properly

Question

I have an Excel workbook broken down into two sheets. On the first sheet, all of the data is organized vertically and I am wanting to data to show up on the second sheet horizontally. I have been able to get it work by using this formula.

=Sheet1!H2
=Sheet1!H12
=Sheet1!H22
=Sheet1!H32

However, when I select the cells and click and drag it down to copy the formulas it will duplicate but the ranges don't work. The new cells will have the formula but cell range is wrong.

=Sheet1!H6
=Sheet1!H16
=Sheet1!H26
=Sheet1!H36

I was expecting it to look like this.

=Sheet1!H42
=Sheet1!H52
=Sheet1!H62
=Sheet1!H72

I've tried using the offset and I'm still having the same problem. I'm not sure why it's no incrementing the way I'm expecting it to, but I hope someone can shed some light on what I'm missing.

To clear it up a bit. I have an excel file with two sheets. Sheet1 and Sheet2. On Sheet1, I'm pulling data from an Access query. Since all of the data is listed in vertical columns, I'm trying to sort the data on Sheet2 horizontally. So I'm linking the cells on Sheet2 to certain cells on Sheet1. The data on sheet one has a variety of parts, numbers, and measurement values recorded.

There will be 10 entries for one part type, number, and measurement that I need to gather. On Sheet2 I just need to have one listing for part type, and number, but all of the measurements need to be captured. For example on Sheet2 say cell F4 needs to link to Sheet1!H2 and cell G4 needs to link to Sheet!H3 and that's for one row of data. The second row on Sheet2 would have cell F5 link to Sheet!H12. What I'm trying to do is get it so I can enter the formula correct on a few rows of data and then highlight and drag those cells to autofill the rest of the column. The problem that's occurring is it will autofill, but it will only increase the linked cell count by 4 instead of by 10.

atclaus atclaus · Accepted Answer · 2016-11-05T21:29:57

Your question is not very clear, but the offset formula should give you what you want. I see you tried it, but lets give it a try again.

I cannot follow where your data is exactly, but this formula in D1 and then dragged down and across will work =OFFSET($A$1,COLUMN(D1)-COLUMN($D$1),ROW(D1)-ROW($D$1))

Excel dragging formula not incrementing properly

1 Answers