Forum Discussion

Anneke_P's avatar
Anneke_P
Copper Contributor
May 04, 2020

VLOOKUP range changes when I cut-and-paste reference cells

I use an excel workbook to automatically generate invoices for my small school. On the invoice sheets are VLOOKUP formulas that reference my accounting spreadsheet. The range specifies all the students for the current month. When I switch months, I copy the previous month's data and insert copied cells, so that the new month's data is now at the top. The VLOOKUP formulas on the invoice sheets change their ranges to refer to the last month's data. They are specified as absolute references, e.g. =VLOOKUP(B8,Ref_Clients!$B$2:$CM$23,4,FALSE) becomes  =VLOOKUP(B8,Ref_Clients!$B$24:$CM$45,4,FALSE). This is really frustrating, as I then have to 'find and replace' 25 sheets. 

 

How can I stop the invoices from automatically changing the reference range when I copy the new month's data?

 

Thanks!

Excel on Mac

2 Replies

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    May 04, 2020

    Hi,

    try using =VLOOKUP(B8,INDIRECT("Ref_Clients!$B$2:$CM$23"),4,FALSE)

     

    see attached demo file

     

    Using Indirect & "   "   quotation marks makes the references locked to the typed string and it works even if the original references are moved.

    Indirect function changes the references to text string, and since the text string does not get affected due to cut-paste-move , so the returned references remain fixed in address.

     

    Take care to use the "  "  quotation marks to qualify the typed addresses as string.

     

Resources