sunysteelfly76 Posted March 15, 2007 Share Posted March 15, 2007 (edited) I'm using VLOOKUP to keep track of my NCAA pool. I did it last year and it worked great (thanks to help from the tailgate). Here's my problem: the formula =VLOOKUP(B2;A63:B98;2;0) works but when I try to copy it (or fill down) the column the A63:B98 changes to A64:B99, A65:B100, ect. down the column. Is there a way to copy the formula so it will change B2, B3, B4, ect. but keep A63:B98 in the formula? I would like to avoid indivdually putting in some 400 formulas. Edited March 15, 2007 by sunysteelfly76 Quote Link to comment Share on other sites More sharing options...
Jimmy Neutron Posted March 15, 2007 Share Posted March 15, 2007 Ya gotta "String" those cells you want fixed with a "$" infront of both the number and letter of the cell locator. Quote Link to comment Share on other sites More sharing options...
Joessfl Posted March 15, 2007 Share Posted March 15, 2007 You are looking for absolute referencing vs. relative. To use absolute, put a $ in front of what you want to stay consistent. (IE $A$63) will alway look at cell A63 no matter how you copy and paste). Or A$63 will leave the 63 the same, and change the A to a B if you copy from A and paste to B. Clear as mud? Quote Link to comment Share on other sites More sharing options...
Coffeeman Posted March 15, 2007 Share Posted March 15, 2007 Is all the data found in col. A & B? If so, just use this: =vlookup(b2,$a:$b,2,false) If that doesn't work, PM me & I'll give you my email to send the file to me. I'm much better when I can see where all the data is.... Quote Link to comment Share on other sites More sharing options...
sunysteelfly76 Posted March 15, 2007 Author Share Posted March 15, 2007 (edited) Thanks guys. I remember the $ being in the formula last year. edit: It works perfectly! Edited March 15, 2007 by sunysteelfly76 Quote Link to comment Share on other sites More sharing options...
Gonkis Posted March 16, 2007 Share Posted March 16, 2007 Shortcut key: The F4 key adds the $ and cycles through the 4 options, i.e. $A$2, $A2, A$2, A2 Quote Link to comment Share on other sites More sharing options...
10MILESDEEP Posted March 16, 2007 Share Posted March 16, 2007 Shortcut key: The F4 key adds the $ and cycles through the 4 options, i.e. $A$2, $A2, A$2, A2 good tip Quote Link to comment Share on other sites More sharing options...
Big Country Posted March 16, 2007 Share Posted March 16, 2007 Shortcut key: The F4 key adds the $ and cycles through the 4 options, i.e. $A$2, $A2, A$2, A2 Never knew this and I am using VLookup and formulas like it on a daily basis. THank you very much 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.