Previous 1 2 Next 16 Replies Latest reply: Dec 13, 2009 10:26 PM by Barry
GUIDOKE Level 1 Level 1 (0 points)
IN EXCEL I USED COPY IN THE CEL WITH THE VARIABLE AND THEN" PASTE SPECIAL" IN THE OTHER CELLS.WHEN CHANGING THE VARIABLE ALL THE OTHER CELLS TOOK OVER THIS VALUE AND USED IT IN FORMULAS.
IS THERE ANY METHOD IN NUMBERS TO DO THE SAME ?

IMAC 20/2,4MHz 1 gb/sd-deu
  • Level 8 Level 8 (41,780 points)
    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
  • Jerrold Green1 Level 7 Level 7 (29,845 points)
    This feature is not included in the Numbers right-click menu, but can be found at Edit > Paste Values.

    Jerry
  • Hwoodwriter Level 1 Level 1 (10 points)
    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.
  • Level 8 Level 8 (41,780 points)
    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
  • sharknca Level 1 Level 1 (130 points)
    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.
  • Jerrold Green1 Level 7 Level 7 (29,845 points)
    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
  • Badunit Level 6 Level 6 (11,350 points)
    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.
  • GUIDOKE Level 1 Level 1 (0 points)
    thanks to all who tried to help me! i try to clarfy my question.
    i have made in numbers 09, 2 maps ,a and b.Map a contains all items of materials and besides a colum in which i can fill in, from time to time, the actual value of each.in map b i made 4 worksheets,one per material(wood,metal,plastics,textile)and a fifth for totalising everything.
    per material i have a colum with all items,another with actual unit value,anoter with the product of the two former and at the bottom a total amount for all items in this category.
    I want that ,when i fill in the map a, the corresponding values in b are actualised so that all total values are adapted. also the last worksheet has to be actualised.
    In excel no problem when using copy and paste/special.
    how to do this in numbers 09 ?
    Is this clear ?
  • Level 8 Level 8 (41,780 points)
    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
  • Jerrold Green1 Level 7 Level 7 (29,845 points)
    GUIDOKE wrote:
    In excel no problem when using copy and paste/special.


    GUIDOKE,

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

    Jerry
  • GUIDOKE Level 1 Level 1 (0 points)
    Hallo Mr koenig ,
    I have a dutch version but discovered in the english help that What i called "map" is "table" and "worksheet" is "sheet". I have been reading the manuals as you suggested but could not find a
    system that works. i.e filling in all actual values in the table and having them transferred automatically to the sheets without being obliged to use copy and paste value each time.
    The only solution i see is to fill in all new values directly in the sheets.
  • Badunit Level 6 Level 6 (11,350 points)
    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.
  • skyle_mac Level 1 Level 1 (0 points)
    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.
  • Barry Level 7 Level 7 (29,205 points)
    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
Previous 1 2 Next