0

The following formula references a cell in a separate sheet:

=('Form Responses 1'!B3)

If I copy that formula to the row below, the formula changes to:

=('Form Responses 1'!B4)

The cells I want to reference are all in the same row and I want to be able to copy the formula down but have the column reference change instead of the row.

For example:

Desired Behavior Current Behavior
=('Form Responses 1'!B3) =('Form Responses 1'!B3)
=('Form Responses 1'!C3) =('Form Responses 1'!B4)
=('Form Responses 1'!D3) =('Form Responses 1'!B5)
=('Form Responses 1'!E3) =('Form Responses 1'!B6)

to row, down the column, the formula is what I am doing, which works fine for my needs, however when I copy and past this it advances like this

=('Form Responses 1'!B4)

Is there an easy way to do this?

2 Answers 2

0

What you are looking for is to lock the formula.

The dollar sign $ is used for "locking" a cell.

  • Form Responses 1'!$B$3 The cell is fully locked.
  • Form Responses 1'!$B3 The column of the cell is locked.
  • Form Responses 1'!B$3 The row of the cell is locked.
  • Form Responses 1'!B3 The cell is unlocked.

EDIT: (following OP comment)

What I need is =('Form Responses 1'!B$3) =('Form Responses 1'!C$3) on the next row down in the same column. Unless there is a better way to do this.

Unlocked cells change the column letter when changing columns and row numbers when changing rows.

The easiest way to achieve what you want is manually:

1. Double click within the cell of the formula ='Form Responses 1'!B$3, highlight the whole of the formula and copy the text.
2. Go to the cell you want, double click within the cell and paste the text you have just copied and manually change the column letter from B to C.

1
  • I want to advance the column letter, while pasting down a single column, it appears to only advance the column if you past in the next column over. I am trying to past down a list of information in my template. What I need is =('Form Responses 1'!B$3) =('Form Responses 1'!C$3) on the next row down in the same column. Unless there is a better way to do this.
    – James
    Oct 10, 2019 at 2:53
0

You want an absolute row reference, but a relative reference for the column based not on changes in the column position but rather the row position. This can't be done with a typical relative column reference. You can manually edit the formulas as pointed out in marikamitsos' answer, but there are also ways to do this using the formula

For example,

  1. Use OFFSET to leverage the row's position
    This is closest to what you requested
  2. TRANSPOSE a range of VALUES in one step
    This or a variation would be a more typical single-formula approach.

1. Use OFFSET to Leverage the Row's Position

=OFFSET('Form Responses 1'!$B$3, 0, ROW()-7)

The OFFSET function returns a reference that has been shifted by a specified number of rows and columns.
OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

The relative row position, as you copy the formula down the column, changes by the exact same amount you want the column reference to change. That difference then is ideal to leverage for OFFSET's offset_columns argument.

  1. Use the formula Form Responses 1'!$B$3 as the cell_reference
  2. Set offset_rows to zero since it should not change
  3. Set offset_columns by calculating the relative row offset. For example, if the first cell containing the formula is B7 then the row offset calculation for offset_columns would be ROW()-7 which is shorthand for ROW()-ROW($B$7)

3. TRANSPOSE a range of VALUES

=TRANSPOSE('Form Responses 1'!B3:E3)

The TRANSPOSE function transposes the rows and columns of an array or range of cells.
TRANSPOSE(array_or_range)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.