Paste Link in Numbers

Question

Level 1

137 points

Paste Link in Numbers

I want to maintain a table in one place. This table will be referenced by other sheets in the Workbook. In Excel one can copy and paste link so that the table in the destination really just points to the original table. It seems that Numbers should be able to do this as when I opened an Excel file in Numbers and saved it as Numbers it did a clean translation. For example, in the sheets that reference the original table it does so as "Cost_Types::Table 1::B1" which is exactly what I need it to do. I read some of the related discussions and saw the "Go to the source cell and type =...." But this table is kind of large and was hoping that there was a more efficient way. Your help is appreciated.

OS X Yosemite (10.10.4)

Posted on Aug 27, 2015 8:30 PM

Reply

Answer 1

Aug 27, 2015 9:02 PM in response to MD_Gene

I am not sure exactly what you are asking. Number does a fantastic job of referencing cells from other tables on other sheets. I think that you are going to have to be more specific of your problem.

Reply

Answer 2

Barry

Level 9

59,861 points

Aug 28, 2015 1:24 AM in response to MD_Gene

HI MD_Gene,

I'm not sure how you "Copy and Paste a link" in MS Excel. In Numbers, you would set up the cell reference you give as an example using these steps:

Click on the target cell (the cell that is to receive the data). Type an = sign.

Navigate to the sheet "Cost_types", and on that sheet to the Table "Table 1", and click on the source cell (the cell that is supplying the data—cell B1).

Done.

What happens after that?

"But this table is kind of large..."

How large? Numbers design model is one featuring several small-single purpose tables working together. It does not handle large tables well.

Regards,

Barry

Reply

Answer 3

MD_Gene Author

Level 1

137 points

Aug 28, 2015 5:11 AM in response to Barry

Wow, Thanks for the quick response. As an aside I look forward to the day I will not be saying stuff like, "this is how Excel did it". But here's an example of the table that resides on each sheet (a sheet for each month of the year). It points to the "Cost_Types" sheet which is where I maintain it.

I do not know how to attach a file. If I did I would attach my sample Numbers file but here is a rendition. The point I want to emphasize is that this table was created by Numbers when I opened the file in Numbers and saved it. Again, I get the "go to destination cell and key in a "=" but I want to copy the entire table. But perhaps there is another solution given Numbers architectural design? Thanks in advance.

0	1	Food	0	16	Home Fuel	0
	2	Gas	0	17	Sherry Hair	0
	3	Home items	0	18	Satellite Radio	0
	4	Wine and Spirits	0	19	Balt Sun/Post	0
	5	Verizon/AT&T	0	20	Gifts	0
	6	Gene's Personal	0	21	Vacation/Vacation	0
	7	Sherry's Personal	0	22	Gene Trailer	0
	8	Car Stuff	0	23	Horse Trailer	0
	9	Contributions	0	24	Other	0
	10	Gene Fishing Sport	0	25	Gene Projects	0
	11	Sherry Horse Sport	0	26	Horses	0
	12	Eating out	0	27	ID Theft	0
	13	Medicine and Dr	0	28	Corral	0
	14	Dogs	0	29	Going Out (e.g. Movies..)	0
	15	Clothes	0	30		0

	Here’s what the formula’s look like in the cells above

	Cost_Types::Table 1::A1	Cost_Types::Table 1::B1		Cost_Types::Table 1::G1	Cost_Types::Table 1::H1
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	.	.		.	.
	Cost_Types::Table 1::A15	Cost_Types::Table 1::B15		Cost_Types::Table 1::G15	Cost_Types::Table 1::H15

Reply

Answer 4

MD_Gene Author

Level 1

137 points

Aug 28, 2015 5:32 AM in response to MD_Gene

I just had another relevation. Even though a table is on a different sheet from a given table, the formula does not need to specifically reference the sheet name to access the cells. I created two tables and one had a formula that referenced the "Dog" table. I then cut the Dog table and move it to a different sheet and lo and behold the formulas did not change. This reinforces one observation from a board helper that "Numbers is a canvas on which one paints tables".

So, my next dive into Numbers will be to see what happens when I have 2 tables with the same name on different sheets?

Any observations?

Reply

Answer 5

MD_Gene Author

Level 1

137 points

Aug 28, 2015 5:41 AM in response to MD_Gene

So when one creates a table with the same name on another sheet, Numbers is smart enough to know that it has to update formulas to specify where the table is in the formula. Wow, This is great. With a little more help from all of you smart people out there, I think I will be achieving "Excel escape velocity" soon and not refer to it.

(and yes, I worked on contracts with NASA for more than a few years of my career.)

Reply

Answer 6

SGIII

Level 8

35,252 points

Aug 28, 2015 5:47 AM in response to MD_Gene

As you have discovered, Numbers doesn't have Excel's feature where you can select a source cell, command-c to copy, go to a destination cell, and Edit > Paste Special > Paste Link.

Instead in Numbers you go first to the destination cell, type =, and then click on the source cell.

The result is the same, a reference to the source cell.

As you've discovered, if you have unique table names then Numbers does not need a sheet name in the reference. If you don't have unique tables then Numbers needs the sheet reference. For example if you have more than one table named 'Table 1' in your document and you wanted to refer to cell A2 in the 'Table 1' that is on Sheet 2, then you would do this:

=Sheet 2::Table 1::A2

Rather than typing that, I find it far easier to go first to the destination cell, type = and then click on the source cell (or select the source range). Numbers adds the sheet name (if needed) automatically.

SG

Reply

Answer 7

Yellowbox

Level 7

20,329 points

Aug 28, 2015 5:57 AM in response to MD_Gene

Hi MD_Gene,

Have a look at the Personal Budget Template in Numbers 3 (Template Chooser > Personal Finance)

On the Transactions Sheet, the Descriptions are all in one column, Categories are all in another column, Amounts are all in another column.

That makes it easy on the Budget Sheet to use SUMIF for the various Categories in the Actual column.

Regards,

Ian.

Reply

Answer 8

Barry

Level 9

59,861 points

Aug 28, 2015 11:12 AM in response to MD_Gene

Hi MD_Gene,

Looking at the image in your second message, it appears you want to reproduce the contents of every cell in the first table into the corresponding cell on the second table. I'm assuming 1). the two narrow columns at the left are NOT part of the table, and that the first wide column is Column A, 2). that the narrow column of zeros in the center represents columns C through F, and 3). that the column of zeros at the right is also not part of the table.

Given those assumptions, here are the steps:

1. Establish the formula in the top left cell of the destination table:

Click on cell A1 of Destination.

Type: =

Click on cell A1 of Source. Result should be similar to the first image below.

Click the checkmark to confirm the formula.

2. Copy the cell, then Paste it to the rest of the cells::

With cell Destination:: A1 still selected, press command-C to Copy it to the Clipboard.

3: Paste it to the rest of the cells:

With Destination::A1 still selected,

Shift-click on Destination::C10 OR press command-A to select all the cells in the table.

Press command-V to Paste.

Note:

In the example, Row 1 of both tables is a Header Row, and has the default Bold and centered attributes set. Copying cell Destination::A1 copies these attributes as well as the formula in the cell, and Paste applies these attributes, as well as the (adjusted) formula to the pasted cells.

Using Paste and Match Style (in the Edit menu) will paste the formula using the default style (or the style that has been set by you) for each target cell.

Cells in Destination below have been left at their default settings:

Summary:

To copy and paste a formula to all cells in a table:

Enter the formula in A1, and confirm it.

Select cell A1, then:

Press command-C, command-A, command-V

Done.

Regards,

Barry

Reply