Copying a formula across rows and columns

Question

Level 1

23 points

Copying a formula across rows and columns

Two questions:

1) how do I get a formula to copy down a column or across a row and adjust for the new row/column spot? So, for instance, the same formula is in B2 that is in C2, but obviously adjusting as it goes down the column...hope that makes sense.

2) Right now when I try and copy and paste a formula I can't because the very first column (which is a darker shade of grey) automatically add the information that is in the dark column into the formula. For instance, the A column has the name of a person so when I create the formula it's putting it in as "(cost dave) + (time dave)=".

How do I get rid of the "Dave" part?

If there is a way to copy a formula down or across will it automatically adjust and get rid of the "dave" part of the equation?

Really hope all this makes sense.

Lloyd

MacBook Pro, Mac OS X (10.6.7), I'm a super awesome person.

Posted on Sep 12, 2018 8:04 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Sep 12, 2018 9:39 PM in response to Lloyd Stein1

Hi Lloyd,

A screen shot would help demonstrate the specific case you are describing.

The default table in a new Numbers document created from the Blank template contains one Header row and one Header column. These are indicated by the grey fill in their cells.

Header rows and header columns have several special properties:

They are, in many functions, excluded from references to a whole column/row
They are excluded from sorts and filters,
They may be 'frozen' to remain on the screen while any body row (or body column) is still visible.
Cells in Header Rows may contain a label for the column. This Label may be used in place of the column better in formulas.
Cells in header columns may contain labels for the row. This label may be used i formulas in place of the row number.

These last two properties are what is dictating the appearance of "cost dave" and "time dave" to appear in your formula. To replace these with the cell addresses, go to Numbers > Preferences, and uncheck the Cell References checkbox.

Cell references may be relative or absolute. Clicking the triangle at the right end of a cell reference token gives the choices to Protect row and/or to Protect column. Checking the box inserts the absolute reference operator ( $ ) before the row or column reference.

Rows that are 'protected' remain the same as a formula is filled down a column. 'Unprotected' rows in formulas filled down a column change to keep the referenced cell's spatial relationship to the cell containing the formula constant. (eg. If the formula in B3 references A2, the same formula filled down to cell B7 will reference cell A6, one column to the left and one row above the cell containing the formula.)

Columns that are 'protected' remain the same as a formula is filled right or left along row. 'Unprotected' columns in formulas filled right or left along a row change to keep the referenced cell's spatial relationship to the cell containing the formula constant. (eg. If the formula in B3 references A2, the same formula filled right to cell E3 will reference cell d2, one column to the left and one row above the cell containing the formula.)

Do these comments address your issue?

If not, please supply a screen shot illustrating the issue and a clearer explanation of tht issue.

Regards,

Barry

Reply