How can I preserve row and column addresses on multiple cells at once in Numbers?

Have You gotten an answer how to achieve this? I'm looking for the same answer.

Simplest explanation or description I can give would be as follows.

two sheets.

Sheet one has a table. Each row represents a footer row of tables on sheet two.

Sheet two has several tables. Each table has a header and a footer row. Header row defines column and footer row contains information pertaining to the above cells in the table.

Tables on sheet two can be sorted differently if desired and columns are not disturbed. Information in footer stays the same no matter how I sort the table.

Back on sheet one. I have the table where the rows represent the footer rows of each table from sheet 2. To quickly achieve this I selected the appropriate cell, pressed = to start formula, went to sheet two, chose appropriate cell from desired table's footer row, and pressed return. Next copied this formula and duplicated it across the row of the table on sheet one. Repeated this process foreach table on sheet two in order to create the completed table on sheet one.

Now on sheet one I'd like to sort and maybe reorder the rows and columns based on my needs. In order to achieve this the cells in the table of sheet one need to be set to Preserve Row and Preserve Column. It is a real pain to go into each cell and make these setting individually one by one. Time consuming is putting it mildly. Each time you make this setting the interface takes you back and forth between sheets. On a large table this can take a great deal of time. Sounds to me like you and I would like a way to select multiple cells and set them at one time to preserve the rows and or columns for which they represent.

Is there a way to achieve this?

When I searched the system for an answer to this question, this issue. This thread was the only appropriate link that was returned. The original question which is the same as mine has yet to be answered. This question is only 2 months old. If it were a year or two years old yes I could see opening a new thread. However at only two months this will be dealing with the same version of the software and the OS etc. When others search for this answer and get this thread they should find an answer.

There is also no need to put up an example file or table. The ones I'm dealing with contain financial information thus there is no way I'm posting them.

It is a simple question. Can "Preserve Row" an / or "Preserve Column" be set on multiple cells at the same time.

YES or NO

If YES how?

The title of the post is this

How can I preserve row and column addresses on multiple cells at once in Numbers?

I restated the Question as follows

Can "Preserve Row" an / or "Preserve Column" be set on multiple cells at the same time.

In both cases it is not asked if multiple cells can be set to....

That is a given.

Step back a second... It is like selecting multiple cells and setting the text color of the currently selected cells to red. This can be done. More than one cell at a time modified because they are currently selected.

Whats is being asked is: if more than one cell is selected at the same time can the settings "Preserve Row" an / or "Preserve Column" be applied. No table I put up will help with that question.

YES or NO

If YES how?

Hi RDAV,

Your fellow users are here to help the best we can. I do think you can make it much easier for us if you can post a question with a specific example (which, of course, would not include "real" financial information) in a new thread if you haven't already found the information you need in an existing thread.

Here are some links that may help you get the most from these Support Communities Work:

Community etiquette

Find answers and ask new questions

Award points, level up, and earn new privileges

SG

I'm sorry, but from your verbal description I really don't follow exactly what you are trying to do. Normally the Preserve Row and Preserve Column settings are applied when entering a formula. In Numbers a formula is normally entered in one cell, right? Then, if needed, that formula is filled (copied) right and/or down into multiple other cells. So, without needed specifics on what you are trying to do, my last answer for the purposes of this thread is: MAYBE.🙂

SG

RD,

Cell expressions that may have absolute references (preserve row or column) often have other references in the same expression that are relative. Numbers would have to be a mind reader in order to change some cell references to absolute and not others when done in bulk.

Jerry

RDav,

As I understand your problem, a row in your table on sheet 1 will reference cells from a single footer row from a table on sheet 2. If so, when you create the formula for the first cell (i.e., when you do the "=" then click on the cell from sheet 2), your next step should be to change the reference to "preserve row". Then copy the formula to the rest of the row. All of the formulas in that row will get the "preserve row" reference. You will have to do this for each row. I am not clear on how you might be creating thousands of formulas without having to do the "= and click on a cell from sheet 2" at least one time for each row. You will have to manually re-do each formula in your first column for each row but the rest of the table should be copy/paste.

So it is a simple fix for your specific problem. No so easy for a lot of other problems. Numbers used to sort differently in previous versions. It sorted the way the OP and you are wishing it did now. But that method also had its problems and workarounds.

Hi nelliebrodie,

The key is what sort of information you are bringing into your report table (ie the table you want to sort). If you are copying a cell that has a sum, for instance, put the sum formula in the report table. Without knowing what you are bringing in it is hard to suggest alternatives.

quinn

You have to design the formulas to be "sort safe" ahead of time. Often it means you have to use indirect() row(), column(), lookup() and other similar functions.

Post specific example of your data (no personal, or sensitive, information, just made up, but representative, data).

Since this is an old thread, suggest starting a new one and including example tables. You can select in Numbers, command-c to copy, click in your post here and command-v to paste.

SG

Ok I see where we are missing one another.

The cell already has a formula in it and is not set to "Preserve Row" and / or "Preserve Column". Doing so before this point will not achieve the same result. I've tried it and it does not work.

You click on the cell containing the formula to select the cell and again to bring up the formula.

Only one entry is in the formula, that of the cell it is set equal to.

To the right of the bubble representing the selected cell in the formula is a down arrow (triangle).

Once selected a balloon drops down with radio buttons to set "Preserve Row" and/ or "Preserve Column".

Setting these in this way, one by one, for a table of several hundred or thousands is, lets just say not much fun and leave it at that.

Thus... Why it is necessary to select multiple cells at one time and apply the setting to all at once.

The purpose of finding a way to achieve these settings at this point in the process is speed and accuracy.

Badunit, thanks for the response. That is exactly right, and that is how I've been creating the table on sheet 1. However not only do I need to be able to sort rows I need to be able to change column order of the table on sheet one as well as eliminate a couple columns. If I set both "Preserve Row" and "Preserve Column" on the first cell in the row, then copy past it across each cell, they are filled with exactly the same thing. As would be expected due to that is what the settings are telling it to do. The "Preserve Column" setting thus can't be set until each are independent cells, form what I've been able to determine. Thus why it would be great, (necessary) to be able to make this setting on multiple cells at the same time.

RD,

I suggest that you think about using the INDIRECT function rather than pinning rows or columns with the Preserve option.

Jerry

There may be some misunderstanding about the Preserve option. It doesn't preserve a reference to a specific cell or cells that may be sorted or rearranged. It's a way to indicate which cell(s) you want the reference to point to if you copy or move its formula. (See 'Refer to cells in formulas' in Help > Formulas and Functions Help.)

SG

<Email Edited By Host>