In Numbers, How can I move a small section with fixed references within the block?

Question

In Numbers, How can I move a small section with fixed references within the block?

I'm making a spreadsheet for some of my hobby projects. For now, the ingredients listed make it look like a recipe. That's just temporary while I work things out, so if the ingredients don't make sense, or it doesn't make sense why I'm adding water, just figure it's how my project works.

I have a fairly simple chart I want to use:

The problem is that I'll be using a number of copies of this chart and I want to copy and paste it so I might have multiple copies of this in rows and columns. Also, some projects take more supplies than others, so in some copies, I'll be deleting rows and in some I'll be adding rows.

The cells in the red block, C6-C13, depend on the values on their row in Column B. But they also all use the number in B15. I created the formula in C6 that looks like this:

Note that the first value, in Column B, stays relative, but the 2nd one, B15, is absolute. So when I created this formula in C6, I copied it and pasted it into C7 to C13, so I didn't have to redo it and so, when I need to delete some rows along the way, everything will still work.

The problem comes in when I copy this and paste it elsewhere. The new copies will still use B15 in the formula, instead of the cell in the same position in the area where I pasted this in.

Is there some way I can keep the absolute reference to B15, but absolute WITHIN the cells I'm copying and pasting?

I don't expect there to be an easy way to do this, but I haven't done much with spreadsheets in something like 20 years and can barely remember just what they can do.

Mac mini (M2, 2023)

Posted on Nov 6, 2025 12:13 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

Nov 6, 2025 6:35 AM in response to HalNineThousand

Numbers allows multiple tables on a sheet. Have you considered putting each project in its own table? That way you can just duplicate the table and each table will preserve absolute references within it.

To duplicate a table click it in then option-drag the concentric circles "bulls-eye" icon in its upper left corner onto the canvas.

Then you modify the duplicate table as needed.

In my example I have two Footer Rows at the bottom of the table. You can add Footer Rows to any table by going to Table > Footer Rows and choosing a number there.

In general in Numbers (as opposed to, say, Excell) you want to avoid lots of empty rows or columns within a table. See the built-in templates at File > New in your menu for examples of effective document design in Numbers.

SG

Reply

Answer 2

SGIII

Level 8

37,532 points

Nov 7, 2025 8:09 AM in response to HalNineThousand

A table is an object on a sheet (tab) in Numbers. Each sheet can have multiple tables. Each table has its own grid of rows and columns of cells. This is different from other spreadsheet apps, which have one big expanse of cells on a sheet.

Option-drag means click the "bull's-eye" hold down the option key and drag with the mouse or trackpad. That duplicates the table.

If you are new to Numbers be sure to have a look at the User Guide at Help > Numbers Help in the menu. Also have a look at the built-in templates at File > New in the menu.

Doing that will quickly give you an idea of the special design characteristics of Numbers. The ability to have multiple tables on a sheet and duplicate them seems particularly convenient for your use case.

If you want to duplicate an entire sheet, note that you can go to the sheet name in the "tab" at the top, click the "v" to its right, and choose 'Duplicate' from the dropdown that appears.

SG

Reply

Answer 3

Nov 7, 2025 9:31 AM in response to SGIII

Ah! I wasn't pressing the <option> key! I misread your statement. It worked pretty much like a drag and drop and everything in the new copy worked just fine. Thank you!

Overnight an idea hit me that would involve @lookup and I'm thinking about changing to that design, which would be more complex, but, in the end, it would make adding a project much easier. Still - now that I can do this, I can set up something functional I can use until I re-learn enough to get the other version working.

Reply

Answer 4

Nov 6, 2025 9:33 AM in response to SGIII

Numbers allows multiple tables on a sheet. Have you considered putting each project in its own table? That way you can just duplicate the table and each table will preserve absolute references within it.

Yes! This is what I want to do. As I said, I haven't used spreadsheets in a long, LONG time, so I've forgotten a lot of the terms. I basically want to be able to copy the table and paste it in, either farther down on the sheet or farther to the right, and when I do that, I want the absolute references to stay absolute within the table, but not absolute to the entire sheet.

It sounds like you have the solution and I'm going to have to do it on my own to get an understanding about it.

To duplicate a table click it in then option-drag the concentric circles "bulls-eye" icon in its upper left corner onto the canvas.

I'm not completely clear what you mean here. I am thinking of "table" as just the rectangle of cells I'm working with, but with what you say, it sounds like you're talking about an actual entity I create within Numbers - like selecting a block and doing something to make it a table. Is that so? If not, I'm not clear what you mean by "click it in the option." When I click on the circle and try to drag, it drags the entire sheet. I take it that it's supposed to only drag the table somehow?

Reply

Answer 5

SGIII

Level 8

37,532 points

Nov 9, 2025 4:48 PM in response to HalNineThousand

HalNineThousand wrote:

Overnight an idea hit me that would involve @lookup and I'm thinking about changing to that design, which would be more complex, but, in the end, it would make adding a project much easier.

Lookups can be highly useful in Numbers, depending on what you are trying to do. However, if you do go for doing a lookup, I recommend you use the modern XLOOKUP function, not the truly ancient LOOKUP function or the old and less flexible VLOOKUP function.

XLOOKUP - Apple Support

(Note that you can always get explanations of functions, together with examples, by going to Help > Formulas and Functions Help in your menu.)

SG

Reply

In Numbers, How can I move a small section with fixed references within the block?

Similar questions