Dave Gundry Posted October 27, 2008 Share Posted October 27, 2008 How do I keep some cell references the same, while letting Excel automatically index to the next when dragging the format tab (that little black square in the bottom right corner of the cell selection)? Example of what I want to do: =A5*B7*C9 =A5*B7*C10 =A5*B7*C11 Example of what Excel does: =A5*B7*C9 =A6*B8*C10 =A7*B9*C11 I thought it was some trick with $ signs, but can't figure it out for the life of me. Please help! Link to comment Share on other sites More sharing options...
Front Man Posted October 27, 2008 Share Posted October 27, 2008 mrexcel.com Link to comment Share on other sites More sharing options...
moverfive Posted October 27, 2008 Share Posted October 27, 2008 How do I keep some cell references the same, while letting Excel automatically index to the next when dragging the format tab (that little black square in the bottom right corner of the cell selection)?Example of what I want to do: =A5*B7*C9 =A5*B7*C10 =A5*B7*C11 Example of what Excel does: =A5*B7*C9 =A6*B8*C10 =A7*B9*C11 I thought it was some trick with $ signs, but can't figure it out for the life of me. Please help! You are close - put the $ in front of what you want frozen. If you drag down, only the number will change since you are staying within the same column. If you drag across, then the letter will change because you are staying within the same row. So from your example, you are dragging down and appear to want to lock in A5, your formula should read: =A$5*B7*C9 Link to comment Share on other sites More sharing options...
-JQ- Posted October 27, 2008 Share Posted October 27, 2008 try using F4 to change your relative references Link to comment Share on other sites More sharing options...
D.Hayden Posted October 27, 2008 Share Posted October 27, 2008 Thanks for that F4 tip... I always type it by hand! Link to comment Share on other sites More sharing options...
Dave Gundry Posted October 27, 2008 Author Share Posted October 27, 2008 Sweet thanks guys - I figured it out - found a help video and just named the constant references (I never knew what that box to the left of the formula bar was) Link to comment Share on other sites More sharing options...
bufit323 Posted October 31, 2008 Share Posted October 31, 2008 Thanks for that F4 tip... I always type it by hand! Just a side note, but a single tap of F4 gets you the double dollar signs, another tap gets you a column only and then a triple tap gets you the row only and the forth tap gets you back to normal. Hope that speeds you along in your excel workings. Link to comment Share on other sites More sharing options...
benos Posted November 5, 2008 Share Posted November 5, 2008 Here's a few handy Excel key shortcuts: Selecting/Inserting/Deleting SHIFT + SPACE Selects row CTRL + SPACE Selects column SHIFT + ARROW Extends selection CTRL + SHIFT + "+" Inserts blank row, even with only cell or even nothing selected. Also inserts copied or cut rows. CTRL + SHIFT + "-" Inserts blank column OR row (if row is selected) CTRL + ARROW Moves selection to end the end of data region CTRL + SHIFT + ARROW Selects entire data region SHIFT + HOME Extends selection to beginning of row! CTRL + HOME Moves selection to A1 CONTROL + SHIFT + HOME Extends selection to beginning of sheet COMMAND + OPT + K Deletes Row, even if only cell is selected COMMAND + OPT + L Deletes Column, even if only cell is selected CTRL + SHIFT + | Select cells in a column that don't match the active (FIRST) cell's value (You must select the column starting with the active cell) It can be useful if you HIDE ROWS Not real valuable - But highlight a group of cells in a column, then the keystroke will check one cell at a time. COMMAND + SHIFT + Z Selects only visible cells in current selection. Think hidden rows. OPTION + CLICK Inserts a row by clicking row number. Inserts a cell by clicking a cell. (Moves previous cell and contents down.) COMMAND + CONTROL + V Paste Special Values CTRL + 9 Hides Rows CTRL + SHIFT + 9 Unhides Rows With only cell selected! CTRL + 0 Hides Columns CTRL + SHIFT + 0 Unhides Colums Editing CTRL + U Edit cell, cursor at end CTRL + ARROW Moves cursor word by word HOME or END Moves cursor to either end of cell CTRL + DEL Delete text after cursor in cell, or to end of line in the Formula bar. CTRL + OPT + RETURN Starts another line in cell Also: COMMAND + CTRL + RETURN CTRL + " Copies the value of the cell above the active cell into the selected cell Formulas COMMAND + SHIFT + T Insert Auto Sum CTRL + SEMICOLON Inserts the date 5/5/08 CMD + SEMICOLON Inserts the time 2:24 PM CTRL + ' (apostrophe) Copies the cell above it, and puts the cursor at the end of the text Formatting CTRL + SHIFT + ~ Apply the General Number format CTRL + SHIFT + $ Apply the Currency Format CTRL + SHIFT + % Apply the Percentage Format COMMAND + OPT + 0 Outlines (border) selection COMMAND + OPT + HYPHEN Remove all outline borders COMMAND + OPT + Apply L, R, Top, or Bottom ANY ARROW KEY outline border COMMAND + SHIFT + F Fills Color COMMAND + OPT + F Clears ALL Formatting (inc. Color and text formatting) COMMAND + D Opens "Sort" Window (I added this one in Excel) If some of the shortcuts don't work for you, it may be because I added the shortcut myself, in View/Toolbars/Customize Toolbars-Menus. be Link to comment Share on other sites More sharing options...
wgnoyes Posted November 5, 2008 Share Posted November 5, 2008 Here's a few handy Excel key shortcuts:...be I don't know some of these keys. "command"? "Opt"? Link to comment Share on other sites More sharing options...
racerba Posted November 5, 2008 Share Posted November 5, 2008 How do I keep some cell references the same, while letting Excel automatically index to the next when dragging the format tab (that little black square in the bottom right corner of the cell selection)?Example of what I want to do: =A5*B7*C9 =A5*B7*C10 =A5*B7*C11 Example of what Excel does: =A5*B7*C9 =A6*B8*C10 =A7*B9*C11 I thought it was some trick with $ signs, but can't figure it out for the life of me. Please help! Here's what you want to put in the first cell: =$A$5*$B$7*C9 When you copy it down to the subsequence cells, you will get what you want. Link to comment Share on other sites More sharing options...
benos Posted November 5, 2008 Share Posted November 5, 2008 Here's a few handy Excel key shortcuts:...be I don't know some of these keys. "command"? "Opt"? Sorry Bill - I should have said I'm on Mac. On a Mac, "Command" is the "Alt" key on a PC. And the "Option" key is the "Start" key on a PC. (I use a Microsoft keyboard.) be Link to comment Share on other sites More sharing options...
norbs007 Posted November 5, 2008 Share Posted November 5, 2008 From your example, racerba got it nailed down to the most simple. Link to comment Share on other sites More sharing options...
Dave Gundry Posted November 6, 2008 Author Share Posted November 6, 2008 Yes he did - that was what I was initially looking for, but naming the referenced cells makes the formulas much more legible, especially when they look like this: =1/((COS(ThetaRad))^4/EL+SIN(ThetaRad)^4/ET+0.25*(1/GLT-2*VLT/EL)*SIN(2*ThetaRad)^2), vs. =1/(((((COS(A11*PI()/180))^4 )/$B$1)+((SIN(A11*PI()/180))^4 )/$B$2)+0.25*((1/$B$3)-((2*$B$4)/$B$1))*(SIN(2*A11*PI()/180)^2)) (that second formula was by another student - guess he didn't know excel can convert to radians, hence all the pi/180's) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now