Forum Discussion
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 studen...
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.
amit_bhola Thanks! I'll try this.