In Google Spreadsheet, force value to be relative and increase by one, when copy/paste and select/drag doesn't work

2
votes

When trying to copy/paste and/or select/drag formula =Indirect("ELA!F"&$A$1) from one cell to the next in a row, in order to get the variable of column 'F', in the formula, to increase by 1 [i.e. =Indirect("ELA!F"&$A$1) to =Indirect("ELA!G"&$A$1) to =Indirect("ELA!H"&$A$1) etc.]

I cannot seem to get Google Spreadsheet to do this automatically for me, and I do not know how to force Google to change that particular variable/value.

Is there a formula that works the opposite of $, thus forcing a value to be relative and increase by 1 instead of forcing it to be absolute?

1
google-sheetscellspreadsheetformula

1 Answers

0
votes

  • Spreadsheets does support relative reference. For relative reference, you'd drop the $. But, the part within the quotes won't change. For example, if you had $A1 and dragged to the next row, it'd change to $A2

  • If you are looking to get a specific column from another sheet TRANSPOSE() would be easier than trying to get a formula to convert the row numbers into a column alphabet.

  • And, here's a similar question on getting the column name from the number. You could do something similar and use Row() for the number.