Inserting row/column....want formula to accommodate!

Question

Level 1

8 points

Inserting row/column....want formula to accommodate!

Using Apple Numbers on MacBook Pro...

So, I have quite a large spreadsheet and have come across two issues....

Firstly, if I try to copy the formulae to adjacent cells, I can do it SO LONG AS all relevant cells are empty of data. If they have any data in them already, then when I drag the yellow circle, it copies the data, rather than the formula?

This then leads to my next issue....

I have forgotten a particular category, so have inserted a new column....how can I get all the formulae to include that new column (whether it's on the far right, or somewhere inside the body of the spreadsheet)?

I'm wondering if a - rather long-winded - way around it would be to delete all the data in the 'Totals' column, to re-write the formula in Row A, and then drag the yellow circle down? Obviously I'd rather not have to do that, and I'm sure that there's something I'm missing.....

MacBook Pro (Retina, 13-inch,Early 2015), macOS High Sierra (10.13.4)

Posted on Apr 17, 2018 11:47 AM

Reply

Answer 1

Apr 17, 2018 12:40 PM in response to Mitramonday

I placed some formulas in column B, then filled those formulas to the right.

Column A contains counting numbers

Column B contains formulas that get the corresponding value from column A

Now I selected column B:

Now I used the yellow fill control to fill the formula to the right:

Check what is in the cells of column C:

To me, this looks correct.

However… if you want the formula in column B to continue to refer to column A, you can use a variation of the formula... like this:

then after filling to the right:

Reply

Answer 2

Apr 18, 2018 9:03 AM in response to Mitramonday

well. you are using the range:

C$2:L$2

this means make row 2 absolute.

I think you just want the range to be C2:L2

then fill down

Reply

Answer 3

Mitramonday Author

Level 1

8 points

Apr 18, 2018 9:03 AM in response to Wayne Contello

Sorry, Wayne - and thank you for attempting to help! However, perhaps I was being unfair trying to explain my issue without screenshots.

So, here I have the correct formula in B2

If I now try to use the yellow handle to copy the formula from B2 to the rest of column B, this happens:

which, obviously is NOT my intention!!

Short of going down each and every row, changing the formula to include the extra rows, I can't seem to work it? Any ideas, now that you've seen my screenshots?!

Reply

Answer 4

Mitramonday Author

Level 1

8 points

Apr 18, 2018 9:04 AM in response to Wayne Contello

Thank you so much, Wayne! I thought I had made the column absolute (not the row)! I knew I must be doing something wrong - and you have managed to point out my error...I'm very grateful!!

Reply

Inserting row/column....want formula to accommodate!

Similar questions