Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel auto calculation formula question.
I'm using the drag function to drag a formula from horizontally. My formula is simple =SUM('Enrollment Data'!H15:L15) When I drag it horzontally I want it to go like this. =SUM('Enrollment Data'!H15:L15) =SUM('Enrollment Data'!H16:L16) =SUM('Enrollment Data'!H17:L17) =SUM('Enrollment Data'!H18:L18) Instead it's going like this =SUM('Enrollment Data'!H15:L15) =SUM('Enrollment Data'!I15:M15) =SUM('Enrollment Data'!J15:N15) =SUM('Enrollment Data'!K15:O15) I tried using $ infront of the letters but it kept the vale all the way through. Can anyone help? Thx. -- jckurk ------------------------------------------------------------------------ jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#2
|
|||
|
|||
Corrected the typo. -- jckurk ------------------------------------------------------------------------ jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#3
|
|||
|
|||
Think through what "relative" reference means. (Sometimes I think it's easier to see relative vs. absolute references in R1C1 notation). When you copy across a row like that, relative references will change the column and not the row in each reference. To get it to look in the same column but different rows, you would copy down the column. Here's what I would do: 1) Make the column reference absolute ($H15) 2) Copy down three rows (assuming you have four blank rows below) 3) Select the three copied rows 4) Select the cell to the right of the original cell 5) Edit - Paste Special - check Transpose 6) It should give you the result you're looking for. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#4
|
|||
|
|||
One way:
Say this is entered in column A: =SUM(OFFSET('Enrollment Data'!$H$15,COLUMN()-COLUMN($A:$A),0,1,5)) If it's entered in a different column, change $A:$A to that column reference In article , jckurk wrote: I'm using the drag function to drag a formula from horizontally. My formula is simple =SUM('Enrollment Data'!H15:L15) When I drag it horzontally I want it to go like this. =SUM('Enrollment Data'!H15:L15) =SUM('Enrollment Data'!H16:L16) =SUM('Enrollment Data'!H17:L17) =SUM('Enrollment Data'!H18:L18) Instead it's going like this =SUM('Enrollment Data'!H15:L15) =SUM('Enrollment Data'!I15:M15) =SUM('Enrollment Data'!J15:N15) =SUM('Enrollment Data'!K15:O15) I tried using $ infront of the letters but it kept the vale all the way through. Can anyone help? Thx. |
#5
|
|||
|
|||
I can't drag the formula vertically because I have other things below that I have formulas in. I also can't make H15 absolute because I need it to change horizontally for each cell (H16, H17, H18 etc.) -- jckurk ------------------------------------------------------------------------ jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#6
|
|||
|
|||
JE McGimpsey Wrote: One way: Say this is entered in column A: =SUM(OFFSET('Enrollment Data'!$H$15,COLUMN()-COLUMN($A:$A),0,1,5)) If it's entered in a different column, change $A:$A to that column reference Uhmm this works. I have no idea why. I don't get it. How is that working? -- jckurk ------------------------------------------------------------------------ jckurk's Profile: http://www.excelforum.com/member.php...o&userid=24152 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#7
|
|||
|
|||
Regardless of where your formula is placed, enter this: =SUM(OFFSET('Enrollment Data'!$H$16:$J$16,(COLUMN(n1)-COLUMN($n$1)),0)) Change the (n1) to match the actual column you enter the formula in, e.g. A1, B1, etc. Good Luck Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=377777 |
#8
|
|||
|
|||
When placed in column A, the formula resolves to
=SUM(OFFSET('Enrollment Data'!$H$15,1-1,0,1,5)) which sums a 1 row, 5 column range starting at H15. When you copy it to column B, the formula resolves to =SUM(OFFSET('Enrollment Data'!$H$15,2-1,0,1,5)) which sums a 1 row, 5 column range starting one row down from H15. And so on. In article , jckurk wrote: JE McGimpsey Wrote: One way: Say this is entered in column A: =SUM(OFFSET('Enrollment Data'!$H$15,COLUMN()-COLUMN($A:$A),0,1,5)) If it's entered in a different column, change $A:$A to that column reference Uhmm this works. I have no idea why. I don't get it. How is that working? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
how do I protect a formula in an excel speadsheet | Excel Worksheet Functions | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |