Find and Replace cell references in Calc.

rrvau1 · Post by **rrvau1** » Thu Feb 07, 2008 11:15 am

Hi,

I may be thick, but if I type a formula into a cell and copy that cell "down"' all the cell references are incremented with each iteration of the formula. For example, =average(A8:A16;"") is copied as =average(A9:A17;""), =average(A10:A18;"") etc. I wish to replace all of the A9, A10 etc with A8. Can this be done?

I have tried wildcards etc but to no avail, any assistance will be received with gratitude.

edit this topic's initial post · Post by **Villeroy** » Thu Feb 07, 2008 11:33 am

Short tutorial on relative, mixed and absolute references in a spreadsheet.

When used in cell B2:
A1 refers to the cell one row above and one column to the left.
Copying down: A2, A3, A4, ...
Copying to the right: B1, C1, D1, ...

$A1 refers to the cell one row above in column A.
Copying down: $A2, $A3, $A4, ...
Copying to the right: remains $A1

A$1 refers to the cell in row #1 and one column to the left.
Copying down: remains A$1
Copying to the right: B$1, C$1, D$1, ...

$A$1 refers to the cell A1
Copying down: remains $A$1
Copying to the right: $A$1

Same with A1:B5, A1:$B5, A1:B$5,... The $ "freezes" the following row or column. Rows and columns without the "$" all are seen relatively to this cell.
Rarely used and Calc specific: Sheet2.A1 vs. $Sheet2.A1 when copied across sheets.

Post by **foxcole** » Thu Feb 07, 2008 10:55 pm

Very nice summary, Villeroy! I hope you copy it out and put it in the Tutorials.

I'd just like to add a note, in case it helps at all, that the $ creates an absolute reference, so $B is always specifically column B.

Without that $, you have a relative reference, meaning a position relative to whatever the current position is, so it changes for each cell where the reference is placed.

edit this topic's initial post · Post by **Villeroy** » Fri Feb 08, 2008 12:09 am

foxcole wrote:Very nice summary, Villeroy! I hope you copy it out and put it in the Tutorials.

~~Done. I put it in the Calc forum with [Tutorial] in the subject.~~

I should have linked [Tutorial] Absolute, relative and mixed references but I was not shure if some moderator will move it to some special place.

rrvau1 · Post by **rrvau1** » Wed Feb 13, 2008 2:54 pm

Hey, thank you for the tutorial, but it don't mean a thing to me. I just want to have the first cell in the average function stay constant with the second cell incrementing with each new line. I would prefer NOT to use MS Works.

RRV

Post by **huw** » Wed Feb 13, 2008 3:15 pm

rrvau1 wrote:Hey, thank you for the tutorial, but it don't mean a thing to me. I just want to have the first cell in the average function stay constant with the second cell incrementing with each new line.

Absolute, mixed, and relative references are how to do what you want. Have a play in an empty spreadsheet with the various types described by Villeroy - it can sometimes be much easier to learn from experimentation.

Post by **acknak** » Wed Feb 13, 2008 4:57 pm

So if your data are in cells A8:A16, and you want an adjustable average in column B, put =AVERAGE(A$8:A16) in cell B8 and copy that down. Calc will adjust the formula as it is copied, but the start of the data range will remain A8.

What are you hoping to accomplish with the empty string argument ("") in your formula?

Be sure to take time to understand the relative/absolute addressing concept--it really is fundamental to working with spreadsheets.

Find and Replace cell references in Calc.

Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.

Re: Find and Replace cell references in Calc.