Forum Discussion

Adm-asst's avatar
Adm-asst
Copper Contributor
Jan 24, 2023

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'...
Formulas and Functions
Shiming's avatar
Shiming
Copper Contributor
Jan 24, 2023

Adm-asst 

 

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. 

  • Adm-asst's avatar
    Adm-asst
    Copper Contributor
    Jan 30, 2023

    Shiming 

     

    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.

Resources