May 1, 2019
Can I prevent down-fill from incrementing *every* cell reference in a formula?
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
You got it. I don't think there is any specific Sheets documentation but here are my cliff notes of how to use them in case others come across this post later.
Absolute vs Relative references (or why do I keep seeing $ signs?)
Normally when you copy a cell with a formula or drag a formula down or across a page all of the cell references (ranges) in that cell will be adjusted based on the cell starting position and the ending or current position. So a formula in Cell A1 that says "=C1" when copied to cell A2 will change to "=C2" and when copied to B3 will change to "=D3". For every one column over the letter increments by one and for every row down the number increases by one. This is relative referencing.
Absolute references (using the $ sign) are for when you want a cell reference (the column, the row, or both) to remain constant when copying the formula to another cell or dragging a cell down a row. You use a $ sign in front of the part that you want to remain constant, $A1 = constant column, A$1 = constant row, $A$1 = fixed position. You can quickly rotate through the various possibilities using the F4 function key. (On some computers, you may need to hold Fn and press the function key for it to work.)
Edit: Note that if you INSERT or DELETE a column/row it will change references so that they still refer to the same cell even if both the row and column are set using $. For example, if a reference points to column D and then a column is inserted between B and C, the original column D will move over and become column E. The reference will now point to column E instead of D.
If you would like a more detailed response, here is good reference with more details.
Last edited May 1, 2019
Original Poster Calvin Loncaric marked this as an answer
May 1, 2019
I think I figured it out: this can be done by using "absolute" rather than "relative" references. More information in this tutorial:
I was not able to find any information about this in the official Google Sheets documentation, but it is also a feature in Microsoft Excel and I guess there is a lot of overlap.
Original Poster Calvin Loncaric marked this as an answer
All Replies (16)
I think I figured it out: this can be done by using "absolute" rather than "relative" references. More information in this tutorial:
I was not able to find any information about this in the official Google Sheets documentation, but it is also a feature in Microsoft Excel and I guess there is a lot of overlap.
Original Poster Calvin Loncaric marked this as an answer
Nov 15, 2019
To follow a specific address you could use INDIRECT, =INDIRECT("A1"), which will always point to the same address. Not knowing what you are trying to do though, I am not sure if this is really the best way to do this. If you want more specific suggestions for you, can you post a new thread for your situation?
Jan 10, 2020
Jan 19, 2020
Jan 30, 2020
What if I don't want this to happen?
I'm using cell references in Sheet2: A3, B3 and C3 have formulae that refer to Sheet1 (e.g. =Sheet1!A3, Sheet1!B3, Sheet1!C3) and I don't want them to change, even if a new column is inserted to the right of column A (a 'new' column B) on Sheet1. I still want Sheet2 to continue looking at Sheet1 A, B & C. What happens is that all the references change on Sheet 2, and the new column is ignored. Is there any way for those references to stay, even if new columns are inserted, or information is copy-pasted?
@Chris Quarrie,
For that situation you should either look into the INDIRECT() function or look into using an ARRAYFORMULA and capturing a range instead of a single cell.
For example, =INDIRECT("Sheet1!A3") will ALWAYS point to that cell even if you add/delete rows or columns.
=ARRAYFORMULA(Sheet1!A3:D10 will bring in cells A3:D10 with a single formula. If you add a column between A and D it will expand to A3:E10. Same if you insert rows between rows 3 and 10: A3:D11. If you add a column to the left or above cell A3 it will just change the range though.
Jan 31, 2020
The ARRAYFORMULA in Sheet2 copies data from Sheet1, but I want to be able to then edit the cells on Sheet2 independently (which cancels the array).
I tried using INDIRECT("Sheet1!A3"), but I want this formula to copy over for the whole table. Unfortunately all cells refer to "A3" instead of changing relative to location (ie. I copy A3 into A4, but the formula stays at A3).
Is there any way to use INDIRECT to refer Sheet2 over to Sheet1, while also changing relative to location when filling the whole sheet?
Last edited Jan 31, 2020
Try this instead then:
=INDIRECT("Sheet1!"&CELL("address",A3))
Jan 31, 2020
I want to enter "MARK" for every standard that will be marked for an assessment. The word MARK will appear in all student sheets, until a grade is entered (Dv, Em, etc).
The problem arises when I want to insert a column in the master table. Student sheets ignore the newly-inserted column, and I have to manually copy-paste the =Master_Table!A3 etc formulas into all student cells for all columns that have not been edited (columns in which marks have not yet been entered).
Thanks for your help, it is deeply appreciated by all staff at my institution!
Jan 31, 2020
Thank you so much