Excel copy/paste question
June 8, 2012 4:53 PM Subscribe
Probably a simple Excel copy/paste issue...
See example workbook. I have a value in cell (E1, in this case), and a formula that references that cell. In the example, that formula in cell E3 is =D3/(1-(E1/100)). I want to copy and paste that formula into a bunch of other cells (a few in my example, but in practice it is hundreds). When I do this, the formula changes to =D3/(1-(E2/100)), =D3/(1-(E3/100)), =D3/(1-(E4/100)), and so on, instead of always staying at E1. This results in a bunch of #VALUE! errors, and depending on how the sheet is laid out, this stair-stepping can sometimes start referencing cells with data in them, producing wildly incorrect results.
So what I'm wanting is when I copy E3 in my example sheet, and paste it into E4 through E8, I want the formula in E4 to say =D4/(1-(E1/100)), and E5 should say =D5/(1-(E1/100)), and E6 should say =D6/(1-(E1/100)), and so on.... the first part changing, but always referencing E1 for the latter part.
Is there a way to make paste do this? Or a better/easier way of accomplishing the same end result? What I don't want is to have rows and rows of the same value (E1's value), just to make the pasting work.
I swear I've done this before, and pretty simply, but for the life of me I can't remember how, and Excel's help is not turning up anything relevant.
See example workbook. I have a value in cell (E1, in this case), and a formula that references that cell. In the example, that formula in cell E3 is =D3/(1-(E1/100)). I want to copy and paste that formula into a bunch of other cells (a few in my example, but in practice it is hundreds). When I do this, the formula changes to =D3/(1-(E2/100)), =D3/(1-(E3/100)), =D3/(1-(E4/100)), and so on, instead of always staying at E1. This results in a bunch of #VALUE! errors, and depending on how the sheet is laid out, this stair-stepping can sometimes start referencing cells with data in them, producing wildly incorrect results.
So what I'm wanting is when I copy E3 in my example sheet, and paste it into E4 through E8, I want the formula in E4 to say =D4/(1-(E1/100)), and E5 should say =D5/(1-(E1/100)), and E6 should say =D6/(1-(E1/100)), and so on.... the first part changing, but always referencing E1 for the latter part.
Is there a way to make paste do this? Or a better/easier way of accomplishing the same end result? What I don't want is to have rows and rows of the same value (E1's value), just to make the pasting work.
I swear I've done this before, and pretty simply, but for the life of me I can't remember how, and Excel's help is not turning up anything relevant.
Best answer: A dollar sign locks the reference. So it looks like you want cell E3 to be:
=D3/(1-(E$1/100))
posted by huckit at 4:58 PM on June 8, 2012
=D3/(1-(E$1/100))
posted by huckit at 4:58 PM on June 8, 2012
Yep, the huckitt has it. The dollar sign causes the row or column reference to remain fixed.
So $E1 will always refer to column E, but the row will change if you copy it up or down, E$1 will always refer to row 1 but the column will change if you copy it left or right, and $E$1 will always refer to cell E1 no matter which way you copy it.
posted by yuwtze at 5:06 PM on June 8, 2012 [2 favorites]
So $E1 will always refer to column E, but the row will change if you copy it up or down, E$1 will always refer to row 1 but the column will change if you copy it left or right, and $E$1 will always refer to cell E1 no matter which way you copy it.
posted by yuwtze at 5:06 PM on June 8, 2012 [2 favorites]
The words you will care about for this in the future are absolute and relative reference.
You can also name a cell (select the cell, type your name into the name box -- it's the one with the cell reference, so E1 in your case) and then use the name of the cell in your formula, so if you named the cell xedrik, your formula in E4 would be =D$(1-(xedrik/100)).
Not terribly necessary to do for this, but really useful in other cases (particularly for ranges).
posted by jeather at 5:09 PM on June 8, 2012 [2 favorites]
You can also name a cell (select the cell, type your name into the name box -- it's the one with the cell reference, so E1 in your case) and then use the name of the cell in your formula, so if you named the cell xedrik, your formula in E4 would be =D$(1-(xedrik/100)).
Not terribly necessary to do for this, but really useful in other cases (particularly for ranges).
posted by jeather at 5:09 PM on June 8, 2012 [2 favorites]
Response by poster: Fantastic. Great info from everyone, thanks so much!
posted by xedrik at 10:26 PM on June 8, 2012
posted by xedrik at 10:26 PM on June 8, 2012
« Older What's good at-home work for a retired nurse... | I'm tempted to sleep in the refrigerator. Newer »
This thread is closed to new comments.
posted by brainmouse at 4:57 PM on June 8, 2012