IWORK 09-NUMBERS -HOW TO COPY AND PASTE A VARIABLE TO CELLS IN OTHER SHEETS

No need to shout,
better to read _Numbers User Guide_ and _iWork Formulas and Functions User Guide_ which every user may download from the Help menu.

Yvan KOENIG (VALLAURIS, France) lundi 28 septembre 2009 15:59:06

This feature is not included in the Numbers right-click menu, but can be found at Edit > Paste Values.

Jerry

This is annoying. A work around I found is to make the cells absolute so that the referenced cells do not move when you paste.

So if your formula refers to cell B1+B2 and you copy it and paste one cell down your formula then reads C1+C2. To keep your formula always pointed to B1 and B2 it needs to be absolute. $B$1+$B$2. This way you can copy and paste your formula without changing the reference points.

Once copied and pasted you can go to the formula bar at the top highlight the cell reference and simply click on the cell you want referenced.
$B$1+$B$2 highlight a cell reference then click on new cell to make formula read $B$1+C2 then change C2 to be absolute if you want.

You will see a small arrow drop down when you cross the cell reference on the right side to change absolute or relative values. Time consuming and frustrating I know.

Hi Jerrold.

It appears that the question was so obscure that two responses are completely different.

Yours offered 'paste values', the other offer a complicated path to get a formula need ing to be edited exactly as what we get with a simple paste.

Of course I don't know what was really wanted 😟

Yvan KOENIG (VALLAURIS, France) vendredi 2 octobre 2009 21:13:57

Not sure I understand the issues described here completely, but maybe the "Mark for move", then "Move" feature (which appears to have the Cut/Paste functionality of Excel) would help.

Yvan,

The question wasn't as clear to me as it must have seemed to the writer. Excel has nested Paste functions, and Paste Special isn't a function but rather a category of paste functions of which Paste Values is one.

Jerry

Hopefully the OP will clarify the problem statement. The only thing I can figure is the use of Paste Special/Paste Link which pastes a reference to the "variable" cell. But even that does not exactly fit the description of the problem and it seems like the hard way create a reference.

I apologize but as I don't know Excel vocabulary, I don't understand what you call a map and what you call a whorksheet/

It would be useful to read the *Numbers User Guide* and the *iWork Formulas and Functions User Guide* to learn and use the Numbers vocabulary. These resources may be downloaded from the Help Menu.
You may use the French terms, I will be able to translate.

Yvan KOENIG (VALLAURIS, France) jeudi 8 octobre 2009 11:21:23

GUIDOKE wrote:
In excel no problem when using copy and paste/special.

GUIDOKE,

What "Paste Special..." function did you use?

Jerry

What is throwing me off here is I do not understand what it was you were doing in Excel that you are unable to do in Numbers. It sounds like what you are really looking for is one of the lookup functions.

It sounds like you have all of your materials listed in Table A and you enter the value/price of each material in Table 1 of Sheet 1 (what I believe you are calling "map a"). In Sheet 2 you have four tables for each type of material (wood, metal, etc) and one that totals those four tables. You want the prices from Sheet 1::Table 1 to show up automatically in the four tables. The fact that the tables are on different sheets is not important to the problem you are trying to solve.



The formula in column C of each of the four tables is
=LOOKUP($A,Table 1 :: $A,Table 1 :: Value)

The footer cell in column D of each of the four tables (you need to have a footer row for this) is
=SUM(D)

The formulas in Column B of the "Total Value" table are
cell B2 =Metal :: D7
cell B3 =Wood :: D7
cell B4 =Textile :: D7
cell B5 =Plastic :: D7
cell B6 = SUM(B)

If you want the last fiver table in another sheet, create a sheet and drag them there.

This is easy. Press the "equals" sign in the destination cell and table, then cruise over to the link cell and table and click on it and press enter. Walah! Why Numbers doesn't include this in the menu or as a right-click I cannot understand.

This thread provides an excellent example of the need to clarify the question before jumping in with an answer. 😉

There's also some misinformation in the post quoted below. The rest of this post addresses ONLY the specific post below, NOT the original question.

Hwoodwriter wrote:
This is annoying. A work around I found is to make the cells absolute so that the referenced cells do not move when you paste.

I don't see this as a 'workaround,' but as the manner in which Copy Paste is designed to work. If you Cell addresses are considered as 'cell locations relative to the current cell' unless the reference is specified as being an absolute address. If you want to Copy & Paste a formula to a different cell and have the pasted formula use the same referenced cells as the original, you can select and copy the formula from the Formula Bar at the top. This copies the formula as text, which remains the same wherever you paste it.

So if your formula refers to cell B1+B2 and you copy it and paste one cell down your formula then reads C1+C2. To keep your formula always pointed to B1 and B2 it needs to be absolute. $B$1+$B$2. This way you can copy and paste your formula without changing the reference points.

Actually if you copy the cell containing =B1+B2, then paste it one cell right, the formula in the new cell will be =C1+C2.
if you copy the cell containing =B1+B2, then paste it one cell down, the formula in the new cell will be =B2+B3.

Once copied and pasted you can go to the formula bar at the top highlight the cell reference and simply click on the cell you want referenced.
$B$1+$B$2 highlight a cell reference then click on new cell to make formula read $B$1+C2 then change C2 to be absolute if you want.

You will see a small arrow drop down when you cross the cell reference on the right side to change absolute or relative values. Time consuming and frustrating I know.

Time consuming, yes. But what's consuming the time is translating the vertical movement (down) of the formula to a horizontal movement (right- from B2 to C2) of the formula's cell reference. Relative cell references move the reference in the same direction as the formula is moved.

Regards,
Barry

We never did find out if any of the numerous answers above was the correct one.