| View previous topic :: View next topic |
| Author |
Message |
jonthebon
Newbie
Joined: 27 Jul 2011
Posts: 2
|
 Posted: Wed Jul 27, 2011 6:31 am Post subject: SOLVED how to refer to a cell? (thanks ken) |
 |
|
In calc how do I refer to a cell? Not the content of the cell but the cell itself. If I have a formula and refer to a cell but then insert a new row or column the reference changes to reflect the new position of the information instead of what I set it at.
=A1
insert new row in row A
now becomes =B1
I want it to constantly say A1 and not change no matter what I do to the spreadsheet.
I tried naming the cell (no luck)
and I tried absolute cell references ($A$1) (no luck)
Sorry if this is a really stupidly simple question. TIA of a reply. 
Last edited by jonthebon on Fri Jul 29, 2011 1:34 am; edited 1 time in total |
|
| Back to top |
|
 |
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1082
Location: UK
|
| Posted: Wed Jul 27, 2011 9:34 am Post subject: |
|
|
When you insert a new row, it pushes the cell and its contents you have previously referred to down, so the spreadsheet alters the reference so that it keeps referring to the same contents.
I have used spreadsheets for over 20 years, and this is how they all work. I have not noticed a way to alter this behaviour, but then I haven't looked much, as it is what I expect and want. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Wed Jul 27, 2011 3:04 pm Post subject: Re: how to refer to a cell? |
|
|
| jonthebon wrote: | =A1
insert new row in row A
now becomes =B1
I want it to constantly say A1 and not change no matter what I do to the spreadsheet |
I think you have confused the terms "row" and "column". "A" is a column label and you would need to insert a new column at column A for =A1 to update to =B1.
A way of preventing the update is to use the INDIRECT function...
in B1 returns the contents of A1.
After inserting a new column at column A, INDIRECT's parameter, the string "A1", does not update to "B1" because it is a string, not a range reference The INDIRECT function converts a string, in this case "A1", into a range reference, in this case a reference to the cell A1.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
jonthebon
Newbie
Joined: 27 Jul 2011
Posts: 2
|
| Posted: Thu Jul 28, 2011 4:01 am Post subject: |
|
|
Ha ha, I did confuse row with column. I was at the very end of the work day after spending the entirety of it on this spreadsheet.
Thank you so much ken, this is exactly what I was looking for! You saved my arse from my boss! Thanks again! |
|
| Back to top |
|
|
|
