Vlookup Break If Move or Copy Sheets

[muhammad susanto]

hi all...

i have vlookup question how to fix problem;
at first, lookup is fine but...for reason..i move/copy my reference tabel e.g name "kki"
then copy/move again name of kki (new data), but vlookup show error..

how to fixed it?

any help, thanks..

.susanto

 

[jmacleary]

Hi there

If you move stuff, excel automatically resets all references to wherever you moved it to, copy shoud do the same. Are you moving/copying to a different workbook? BUT, if you are changing the name of the range, then excel may wel get confused.

 

[muhammad susanto]

hi...i'm not change range anything...
i have 2 sheets :
sheet 1 "main"
sheet 2 "kki" as reference..

then i create new sheet with name "kki" EXACTLY the same range with "kki" before. after that sheet "kki" delete then changed with new "kki"
the result is link of vlookup is broken..vlookup #ref ....

how to fix this problem...i just want change a new sheet reference with new sheet reference with the same layout...

i hope someone would me suggestion or tricks//

 

[jmacleary]

then i create new sheet with name "kki" EXACTLY the same range with "kki" before. after that sheet "kki" delete then changed with new "kki"
the result is link of vlookup is broken..vlookup #ref ....

I'm sorry if I didn't make the explanation clear enough. The problem occurs at the point where you say 'after that sheet "kki" delete' - at that point the references are broken because they point to a sheet that doesnt exist. Renaming the new sheet to the old name then has no effect.

I suggest instead of deleting sheet kki, you clear its contents, then copy in any new new contents. This will keep the references.