Skipping Cells When Creating Lists (Locking Individual Cells?)

Question

DreamOnMan92 Author

Level 1

13 points

Skipping Cells When Creating Lists (Locking Individual Cells?)

Using Mojave 10.14.5, Numbers 6.1 (6369).

Short version: How can I lock individual cells within a worksheet, if possible?

Long version:

I have lists of numbers here on one sheet, which are linked to cells on other sheets in the same Numbers document.

The cells in the "RS" columns below are linked to columns of cells on another sheet, where I can just put a link in the first cell and drag down to fill out the rest.

The rows where the Result column is already filled (the selected cells in the screenshot), will not need more text, so when I repeat the procedure for the "Game 4"and "Game 5" sections, I want to have Numbers skip over the selected cells in those columns.

Can I lock the selected cells, and the rest of those as described, so that when I drag down cells to create a list, some cells are skipped over into the next unlocked cell(s) ? And if so, how?

Posted on Jul 1, 2019 2:56 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,891 points

Jul 1, 2019 6:33 PM in response to DreamOnMan92

Short version: How can I lock individual cells within a worksheet, if possible?

Not a supported feature. You can lock objects, such as shapes, text boxes, or tables, but 'cells' are not 'objects', and cannot be locked.

Long version: Can I lock the selected cells, and the rest of those as described,

No. See above.

…so that when I drag down cells to create a list, some cells are skipped over into the next unlocked cell(s) ?

Maybe.

…And if so, how?

Give this a try. It's an exploration, and I've not gone too far into the woods, but it may give you a starting point for your own exploration (or inspire other contributions).

Steps:

Constructed the table on the left, with one column of names. (Table 3)

Constructed the table on the right (Table 4) (with several more rows than are visible in the image above).

Left column A empty, and entered text ( 'results' ) in several cells of column C.

Opened Sort & Filter, and selected Filter.

Created the filter rule shown in the Filter Inspector, then clicked the Filter checkbox to activate the filter, which hid all rows containing 'results' in column C.

Selected cell A2 of this table, typed = to open the Formula Editor, then clicked A2 of Table 3 to create a cell reference, and clicked the green checkmark to confirm the formula and close the editor.

Grabbed the Fill control (yellow circle) and dragged down to fill the formula down to the last cell in column A.

With all cells (or all visible cells—I didn't check) still selected, I pressed command-C to Copy, then went to the Edit menu and chose Paste Formula Results to paste the results, and remove the formula(s).

Results at this point are shown above.

Final step was to click the Filter checkbox to uncheck it and turn the filter off.

Regards,

Barry

Reply

Answer 2

DreamOnMan92 Author

Level 1

13 points

Jul 1, 2019 9:57 PM in response to Barry

Hi Barry,

I've tried following your steps a few different times, and also tried it with the sheet I'm working on. Your method works... kinda.

Compare. On the left is the reference data. On the right is the Results page where the rows to be skipped are filtered.

Filtered rows 7, 8 and 10 on the right sheet. Created cell reference at the beginning of that table, dragged down to the same row as in the left sheet.

Rows 9 and 11 on the right should read "Key, 6, Martinez, 5" (what row 7 is on the left sheet) and "Key 1, Martinez 2" (row 8 on the left sheet), and further on down the lists for both tables. In using the filter on the right table, the "skipping" part worked (rows 7, 8 and 10 on the right one are blank), but the data didn't translate correctly.

Thoughts? Ideas?

Reply

Answer 3

DreamOnMan92 Author

Level 1

13 points

Jul 3, 2019 5:46 PM in response to Barry

Hi Barry,

Each row simulates a game in a best-of-5 "series" between baseball teams. In the results table, each game has a "winner", which is denoted by either "CLE" or "NYY". I modified the table itself a bit for easier reading here:

Columns Q and R track the number of wins a team has in each series. These are simply COUNTIF functions for how many occurrences of "CLE" or "NYY" in that row. Of course, a "series" ends whenever one team wins 3 games, so at the beginning here if the series is "Indians 3, Yankees 0" or vice versa (indicated by columns Q and R), then the cells in the Game 4 columns (K, L, and M) and Game 5 columns (N, O, and P) should be blank.

In the first 15 trials shown, cells K9: P10, K12:P12, and K14:P15 will be blank.

The same goes for if a Game 4 is "needed"–in that case if the series becomes "Indians 3, Yankees 1" or vice versa then the Game 5 columns will be blank. (Column S indicates the overall series result, and also denote which remaining cells in that row should be left blank, if necessary. I used this column as the basis–whether the cell was blank or not–when I tried your first solution with the filters.)

Hope this helps.

Reply

Answer 4

Barry

Level 9

59,891 points

Jul 1, 2019 11:47 PM in response to DreamOnMan92

"the "skipping" part worked (rows 7, 8 and 10 on the right one are blank), but the data didn't translate correctly"

That is what happened in my exploration as can be seen in the screen shots. The 'hidden" rows were empty, and the data in the "visible" rows was transferred to the same row as it was in on the original table.

Note: Numbers "sheets" do not have rows or columns. Sheets are large canvases on which objects, including 'tables' are placed. Tables have rows and columns of cells.

Skipping Cells When Creating Lists (Locking Individual Cells?)

Similar questions