Excel - Need help with formulas especially from data validation

New Contributor

Hi, I have a large Excel spreadsheet.  See the following formulas.  A2:C10 is from data validation.   =IFERROR(VLOOKUP(M20,'Add AC1'!A2:C10,2,0),"")

and another one

=IFERROR(VLOOKUP(M20,'Add AC1'!A2:C10,3,0),"")


When creating a new row to continue a list, I have to change A_:C_ manually in keeping the same A2:C10 because data validation has from row A2 to C10.  Is there a way I can set it automatically to stick to A2:C10? Please let me know. 


Thank you

2 Replies





You can add an $ in front of the cell name, say $A2 means you want to lock the column and move the row when you drag the formula into another cell. A$2 means the row will be locked and the column will be changed. $A$2 means you lock the cell and don't want it to be changed. In your case the formula can be changed to $A$2:$C$10. You can press F4 to change A2 into $A$2 faster. 



Hi Shiming,


Many many many thanks for this tip!  It works and very helpful!  It saved a lot of time too.  Hip Hip Horray!


Thank you.