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

[Anneke_P]

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!

[#2]

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.

[#3]

@amit_bhola Thanks! I'll try this.