Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Prevent Formulas from Advancing
Does anyone know how to prevent a formula from advancing when you copy it down. My situation is this, I'm trying to use the VLookup function to pull data from a table. When I copy the formula down, it doesn't just advance the lookup value number, it also advances the table array. I do want the lookup value to advance of course, but not the table array. I want the table array to be the same all the way down. For example, the formula looks like this: =VLOOKUP(B2,'September 2005'!A2:G296,1,FALSE) When I copy it down it changes to: =VLOOKUP(B3,'September 2005'!A3:G297,1,FALSE) and the numbers A3:G297 keep advancing as I copy the formula down. Like I said, I do want the B2 to advance to B3, but I do not want the A2:G296 to change when I copy the formula down. Is there a way to do this? -- dallased25 ------------------------------------------------------------------------ dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544 View this thread: http://www.excelforum.com/showthread...hreadid=482033 |
#2
|
|||
|
|||
Prevent Formulas from Advancing
=VLOOKUP(B2,'September 2005'!$A$2:$G$296,1,FALSE) The $ in front of the row and column stops the cell reference from changing as you copy it. Good Luck. Does anyone know how to prevent a formula from advancing when you copy it down. My situation is this, I'm trying to use the VLookup function to pull data from a table. When I copy the formula down, it doesn't just advance the lookup value number, it also advances the table array. I do want the lookup value to advance of course, but not the table array. I want the table array to be the same all the way down. For example, the formula looks like this: =VLOOKUP(B2,'September 2005'!A2:G296,1,FALSE) When I copy it down it changes to: =VLOOKUP(B3,'September 2005'!A3:G297,1,FALSE) and the numbers A3:G297 keep advancing as I copy the formula down. Like I said, I do want the B2 to advance to B3, but I do not want the A2:G296 to change when I copy the formula down. Is there a way to do this? -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php...o&userid=19838 View this thread: http://www.excelforum.com/showthread...hreadid=482033 |
#4
|
|||
|
|||
Prevent Formulas from Advancing
Awesome, thanks for the info! One more question though. Is there a way to make it so that it puts the $ in there automatically the first time around, rather than me having to go in there everytime and edit the formula? -- dallased25 ------------------------------------------------------------------------ dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544 View this thread: http://www.excelforum.com/showthread...hreadid=482033 |
#5
|
|||
|
|||
Prevent Formulas from Advancing
As I said, as soon as you are entering any part of any formula press the F4
key before moving on or re-select the reference and press F4 Repetitive presses toggles through the four options (In no particular order) Absolute Row, Absolute Column Relative Row, Absolute Column Absolute Row, Relative Column Relative Row, Relative Column -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "dallased25" wrote in message ... Awesome, thanks for the info! One more question though. Is there a way to make it so that it puts the $ in there automatically the first time around, rather than me having to go in there everytime and edit the formula? -- dallased25 ------------------------------------------------------------------------ dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544 View this thread: http://www.excelforum.com/showthread...hreadid=482033 |
#6
|
|||
|
|||
Prevent Formulas from Advancing
You could also give that range a nice name.
Select A2:G296 on that sheet. Insert|Name|define give it a nice name (myTable) then you can use: =vlookup(b2,myTable,1,false) If there's nothing else in that 'september 2005' sheet, maybe you could use the whol column: =vlookup(b2,'september 2005'!a:g,1,false) ====== As an aside, it looks like you're returning the value you're looking for (column 1). If you're just looking to see if that value exists in that first column, you could use the =match() worksheet function: =if(isnumber(match(b2,'september 2005'!a:a,0)),"It matched","not there") Debra Dalgleish's has some notes you may like: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) dallased25 wrote: Awesome, thanks for the info! One more question though. Is there a way to make it so that it puts the $ in there automatically the first time around, rather than me having to go in there everytime and edit the formula? -- dallased25 ------------------------------------------------------------------------ dallased25's Profile: http://www.excelforum.com/member.php...o&userid=28544 View this thread: http://www.excelforum.com/showthread...hreadid=482033 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |