I’m stuck. I have a spreadsheet, which using a Vlookup and an Index formula and referencing a named range, returns a result based on an ID number in another cell. This works. However, I am using VBA to ‘translate’ the spreadsheet… this simply deletes the named ranges then re-adds them to another sheet (The code works to toggle between the two sheets – the references stay the same, but the sheets change between a German and an English sheet). The sheets are both re-named and the unreferenced sheet gets hidden.

Everything seems to go as planned, but the cells with the Vlookup/index formulae return a #ref error in nearly all (but strangely not all formula that use the reference to the named range.). When I toggle back to the original language, the formula works.

Formula is:

=INDEX(Leistungen;VERGLEICH($B4;IDs;0)) //Leistungen and IDs are the named ranges which move.

Is there something vital I’m missing here? I can see the named ranges in the drop down menu and Excel takes me to them when I click on them, but when I try to refer to the named ranges in the formula bar, they aren’t there but reappear when I move them back to the original position…

I’ve tried totally deleting all of the referenced named ranges before re-adding them (With vba), and I’ve also tried simply using Thisworkbook.Names(“Name“).refersto = range , to edit the reference for the existing named ranges but both return the same result.

Manually changing the referenced cells gives the desired result.

My guess is that the named ranges have some kind of anchor in excel somewhere that isn’t affected by this process, because of the fact that returning them to their original position works again.

I’m using Excel 2016. Would be so happy to have this working!!