# Thread: Copy/Paste cells (2003)

1. ## 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.

2. ## 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. ## 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)

4. ## Re: Copy/Paste cells (2003)

Thanks Jerry, I very much appreciate the additional info!

5. ## 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. ## 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. ## 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...
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>

#### Posting Permissions

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