Forum Discussion
Excel - Need help with formulas especially from data validation
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
Hi
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.
