"insert proof" VLOOKUP & HLOOKUP ?

  • Thread starter Thread starter Jorge Fonyat
  • Start date Start date
J

Jorge Fonyat

I have rather complex spreadsheets with many table lookups.
The regular lookup functions work quite well but inserting rows/colums
my result in totally wrong searches. (updating spreadsheets several
moths after having developed them becomes a risky task)
I saw a few suggestions to fix this limitation using ROW or MATCH.
Is there a more structured solution? (even using the above mentioned
functions)
I'd lake to address the general case: tables NOT starting in A1.
Thanks in advance,
Jorge Fonyat
 
If you make sure the lines are inserted IN the table, not at the end or at
the beginning, Excel adjusts the formulas automatically. Try adding dummy
start- and end lines, hide them if necessary.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Niek,
Truly good news ! Thanks.
I didn't realize that.
In fact it solves my problem in only part of my situations: when the
different spreadsheets belong to the same file.
Unfortunately I have several linked worksheets that are in different
files. (loaded simultaneously)
Using variable names has permitted to keep them properly linked and
still keep the freedom to change them without creating a mess.
But I don't think that in this case the row/column numbers would be
automatically updated.
Thanks and regards, Jorge Fonyat
 
Back
Top