Results 1 to 7 of 7
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Copy/Paste cells (2003)

    First, I can barely run a calculator least of all Excell (see; I can't even spell it!)

    I want to copy a formula (down column D) where one of the references is constant (M5), but the others are relevant to the row.
    =(C5*M5)+C5
    Everything I've tried thus far makes 'M' relevant to the row:
    =(C5*M5)+C5
    =(C6*M6)+C6

    At this point I don't know enough terminology to even find an answer in the Lounge Search. Any help greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste cells (2003)

    To make a reference 'absolute', put $ signs before the row and or column reference.

    =C5*$M$5+C5

    Note: if you select a cell or range while editing a formula, pressing the function key F4 cycles between the four options M5, $M$5, $M5 and M$5.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste cells (2003)

    Hans:

    OK, this is getting a little too spooky. Do I have a webcam in my office I'm not aware of? Everytime I have a problem, you're right there to solve it! You are, indeed, my guardian angel. Your solution (of course) is perfect (and thanks for the F4 hint - very cool)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste cells (2003)

    Thanks Jerry, I very much appreciate the additional info!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Copy/Paste cells (2003)

    Bryan,

    an explanation and one more piece of additional info based on Jerry's tip.

    What Jerry is doing is naming the cell with the number (say B1) so that a name can be used in any formula, including those you fill down. The cell referenced by a name is not changed when you use the fill process, unlike cell references like M5 - as you've discovered.

    When you use the dialog Jerry mentions (Insert | Name | Define), Excel suggests a name for the cell (B1 in this case). The suggestion comes from Excel looking at the cell to the left (A1 in this case), which is where Jerry typed the word "VAT". I do the same thing as Jerry for things like the VAT percentage - type a word in a cell then type the value in the cell to the right. That gives 2 advantages:
    - when you look at cell B1, you can see a label to the left to tell you what it is
    - when you look at a formula, you can see the name of the value ("VAT") rather than the number (17.5%) - this way you know what the formula is doing by seeing the name rather than the number.

    There is another way to name a cell. Say you already have A1 set to "VAT" and B1 set to 17.5%. Make sure B1 is selected. Now click on the "Name Box" - you should see the cell reference (B1) before you click. This box tells you the current cell - it is located just above the "A" that labels column A (at least on my 2003 Excel). When you click on the Name Box, the cell reference B1 is highlighted. You can now type the name (VAT) in the Name Box and hit enter. You've just created a name. If you go thru the dialog that Jerry mentioned, you'll see the new name in the list.

    With this approach, you do NOT have to have a name in cell A1. However, I strongly recommend having it there anyway, as Jerry did, so that when you look at B1 you see its name to the left. Excel doesn't really care what's in A1 but it helps you.

    Hope this helps.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy/Paste cells (2003)

    A slightly faster way of naming the cells (especially if you have a lot) is to use insert name - create.

    In your example: Highlight both the cell with the name and the value

    Insert - name - create
    "left column" will be checked
    [ok]

    While about the same as your method with only 1 name, if you have a list in A1:A10 of names and want to name B1:B10 with names contained in A1:A10, select A1:B10 and

    Insert - name - create
    "left column" will be checked
    [ok]

    You will 10 named cells created in 1 action. The procedure works for cells to the left or even above or below the "desired names".
    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy/Paste cells (2003)

    Bryan

    Further to Hans' answer, I have a habit, especially when I have lots of variables, is to name them.

    Say I have to do lots of calculations with VAT at 17.5% in them I type the word VAT in a cell and the cell directly to the right of it I type in 17.5%. I highlight the cell with the number in it and then:

    1) Insert | Name | Define...
    2) Click Add
    3) Click OK

    I can now do lots of calcualtions like this

    =VAT * 23

    You can use different names for different values all over your spreadsheet and use fill down without effecting the referencing......just an idea for future <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •