6

Is there a way to fill a formula across columns (i.e., horizontally) but have the cell references in the formula increment the row number instead of the column?

I have a summary sheet that links to totals on back-sheets and this would speed up the linking of cells. Lets say A1's formula is =Sheet2!G5. I want the value of B1 to be equal to =Sheet2!G6 and NOT =Sheet2!H5.

How can I get Excel to do this?


Clarification
Each row on my summary sheet needs to pick-up 3 values from a back-sheet.

A1 =Sheet2!G5 B1 =Sheet2!G6 C1 =Sheet2!G7
A2 =Sheet3!G25 B2 =Sheet3!G26 C2 =Sheet3!G27

I would like to save time by only manually setting A1 =Sheet2!G5 and A2 =Sheet3!G25 then somehow offsetting the values in B1,C1, B2, C2.

5
  • In which cell do you want the formula =Sheet2!G51? Also, please describe exactly what you'd like to do.
    – User15
    Jul 28, 2015 at 20:34
  • I would like A2 to have a formula of =Sheet2!G51, A3 would have =Sheet2!GH52 Jul 28, 2015 at 20:40
  • You can drag or copy-past. The formula will change accordingly.
    – User15
    Jul 28, 2015 at 21:24
  • 1
    That won't work. My lines on the summary sheet go horizontally while the back-sheets go vertically. Jul 28, 2015 at 21:26
  • (1) You mention =Sheet2!G50, =Sheet2!G51, and =Sheet2!GH52.  I assume that the H in GH52 is a typo.  (2) Please explain what you've done to your "summary sheet" to cause A1 - A2 - A3 to be aligned horizontally.  Please do not respond in comments; edit your question to make it clearer. Jul 29, 2015 at 4:53

2 Answers 2

6

In A1:

=OFFSET(Sheet2!$G5,COLUMN()-1,0)

This returns a reference to Sheet2!G5, and when dragged across to B1 returns a reference to Sheet2!G6. The same can be done in A2:

=OFFSET(Sheet2!$G25,COLUMN()-1,0)
2
  • I came across this while researching, I didn't want to use it because it would throw off people who would not understand the formula. I was hoping to have some kind of setup where A1 =Sheet2!G5 and a formula in B1 and C1 that would offset. --- Just a note for readers since it wasn't obvious for me the first time I came across this. The -1 is to offset the column() value of 1 since A is in the first column. If you're working on column C for example. The formula would become column()-3 Jul 29, 2015 at 18:20
  • You can try TRANSPOSE() in an array formula, or use FORMULATEXT() to get the cell reference from A1 for use in your OFFSET() function. However, if your users do not understand the simple OFFSET() function in my answer I do not think they will understand anything that solves your problem. In the future please list all of your requirements, as well as what you've already tried in your question.
    – Kyle
    Jul 29, 2015 at 19:34
3

I lied, you totally can do this in a way that creates easy to read references for your users. Anywhere in your workbook (aside from row one, where you are putting the end result), type this:

=Sheet2!$G5

And autofill down to however many rows you need. Then copy all of those cells, select A1 and paste special - transpose.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .