JayB Posted November 21, 2007 Share Posted November 21, 2007 Is there any way to use the autofill function to drag a formula from one cell to the next in a way that keeps one of the cells referenced in the formula constant? In other words, if I have a bunch of cells that I want to subtract the value of cell A1 from, is there a way to use the autofill function so that I get: B2-A1, C2-A1, D2-A1, etc. instead of B2-A1, C2-A2, D2-A3, etc when I try to drag the formula from one cell to another? Quote Link to comment Share on other sites More sharing options...
TrogdortheBurninator Posted November 21, 2007 Share Posted November 21, 2007 placing dollar signs in front of the column and row locks that cell. you can also cycle through locking iterations by pressing F4 with the cursor next to the prospective locked cell in the formula. Quote Link to comment Share on other sites More sharing options...
gertlush Posted November 21, 2007 Share Posted November 21, 2007 If you put a '$' sign in front of the A or the 1 I believe it holds it constant, i.e. $A1 just keeps the A the same but the 1 will change and vice versa. Quote Link to comment Share on other sites More sharing options...
chucK Posted November 21, 2007 Share Posted November 21, 2007 (edited) In the formula put a "$" before the value you want to remain static. E.g. if you want to keep the row static A$1, column static $A1, both static $A$1. whoa!! geeks popping out of the woodwork. Like whack a mole! Edited November 21, 2007 by chucK Quote Link to comment Share on other sites More sharing options...
TrogdortheBurninator Posted November 21, 2007 Share Posted November 21, 2007 i.e. =B2-$A$1 Quote Link to comment Share on other sites More sharing options...
iciclespyder Posted November 21, 2007 Share Posted November 21, 2007 try B2-A$1 Quote Link to comment Share on other sites More sharing options...
jon Posted November 21, 2007 Share Posted November 21, 2007 Put $ in front like $B$1 to keep it constant in an autofill formula. Quote Link to comment Share on other sites More sharing options...
jon Posted November 21, 2007 Share Posted November 21, 2007 Holy shit I know where to go now if I'm stumped in Excel. Quote Link to comment Share on other sites More sharing options...
JayB Posted November 21, 2007 Author Share Posted November 21, 2007 Dear God - that was fast! Never had to do that before. Definitely beats typing A1 a gazillion times. Many thanks for the help. Quote Link to comment Share on other sites More sharing options...
pope Posted November 21, 2007 Share Posted November 21, 2007 Here's another way. Click in A1, then "insert","name" and "define". In the "names in the window" line type "k" and click OK. Back in A1, type the value that you want. Enter your data in column B, then in C1 type " = B1-k " (no quotes). Then drag the corner of B1 down. I wish I knew a lot more about the program. It seems there's nothing it can't do. Quote Link to comment Share on other sites More sharing options...
selkirk Posted November 21, 2007 Share Posted November 21, 2007 Screw Excel, get Matlab Quote Link to comment Share on other sites More sharing options...
archenemy Posted November 21, 2007 Share Posted November 21, 2007 Screw MatLab. Get Astroids. Quote Link to comment Share on other sites More sharing options...
builder206 Posted November 21, 2007 Share Posted November 21, 2007 x^2/3 + y^2/3 = 1? Quote Link to comment Share on other sites More sharing options...
archenemy Posted November 21, 2007 Share Posted November 21, 2007 I feel like such a square. Thanks E+6 Quote Link to comment Share on other sites More sharing options...
ClimbingPanther Posted November 21, 2007 Share Posted November 21, 2007 delete selkirk.m Quote Link to comment Share on other sites More sharing options...
Off_White Posted November 21, 2007 Share Posted November 21, 2007 Screw MatLab. Get Astroids. Quote Link to comment Share on other sites More sharing options...
Dechristo Posted November 25, 2007 Share Posted November 25, 2007 should soothe his burning, itching gums and reduce the swelling of his lips. Perhaps, he's aghast to read that it's prescribed to be applied to his anus first. Quote Link to comment Share on other sites More sharing options...
Bill_Simpkins Posted November 25, 2007 Share Posted November 25, 2007 Screw Excel, get Matlab Screw Matlab, code it yourself. Quote Link to comment Share on other sites More sharing options...
i_like_sun Posted November 25, 2007 Share Posted November 25, 2007 The new version of Office is a bunch of crap. I used to know how to use Excel super well, now I guess only Bill can use it. Quote Link to comment Share on other sites More sharing options...
crackers Posted November 26, 2007 Share Posted November 26, 2007 afaik, ctrl-f3 calls the "name" definition window, or if the appropriate cell is active, you can type in a name in the left side of the formula bar. If you want to make a cell absolute, you should be able to just hit f4 when the cell is active. First f4, $a$1, second, $a1, third a$1 and fourth a1. That's off the top of my head. You should name things the right name. Do not use something like average, or mean, or any other function name or single letter that might create confusion. MatLab is good for some stuff, STATA is good for others, coding is good for others, and excel is great for a simple GUI that people who shouldn't be allowed near those other systems or the data. Quote Link to comment Share on other sites More sharing options...
crackers Posted November 26, 2007 Share Posted November 26, 2007 btw, the rnd function is total crap. if you need a random number generator, don't use rnd()... Quote Link to comment Share on other sites More sharing options...
chucK Posted November 28, 2007 Share Posted November 28, 2007 New question How do I format the top row (or rows) to be a header so that when I scroll down the top row stays put (so I can keep seeing the variable labels)? Quote Link to comment Share on other sites More sharing options...
jon Posted November 28, 2007 Share Posted November 28, 2007 Well on the mac at the top of the slider bar there is a blue button you can grab and slide down. That's childs play! Quote Link to comment Share on other sites More sharing options...
TrogdortheBurninator Posted November 28, 2007 Share Posted November 28, 2007 click on the first column of the row below those you want to freeze. go to Window: Free Panes Quote Link to comment Share on other sites More sharing options...
TrogdortheBurninator Posted November 28, 2007 Share Posted November 28, 2007 btw, the rnd function is total crap. if you need a random number generator, don't use rnd()... when has the RAND() function failed you? Excel 2003 and newer has a revised random number generator algorithm. Even the old algorithm was sufficient for probably 99.9%+ of all users. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.