Moving worksheets in Excel/LibreOffice

[staffordian]

I have two spreadsheets which are essentially similar in terms of worksheet names and layout, though the first has one or two extra worksheets.

I want to move a sheet from the first to the second and am familiar with the process for doing this.

However when moved, all the formulae in the worksheet which is now in the second spreadsheet refer back to the original one, ie they are absolute references, whereas what I want is for the formulae to reference the equivalent cells in worksheets of the second spreadsheet, ie to be relative. I can manually edit them assuming I can find them all(!) but it is a laborious process which I have to repeat from time to time.

Is there any way to move a worksheet without keeping absolute references in formulae?

Thanks

Staffordian

PS It's all Kiloran and Itsallaguess's fault for updating the incredibly useful HYPTUSS and me having to then update my heavily modified version of it

[Slarti]

I don't think so, but a Ctrl+H on the moved sheet should allow you to remover the reference to the other book by replacing the path with nothing.

Do not use link manager otherwise you will just end up with numbers.

Try it on a copy first.

Slarti

[staffordian]

Thanks Slarti, that's a far quicker way than my manual searching and editing. Just tried it on a copy and it works a treat.

Have a virtual rec sir!

Staffordian

[Itsallaguess]

P.S. It's all Kiloran and Itsallaguess's fault for updating the incredibly useful HYPTUSS and me having to then update my heavily modified version of it

Thanks, I think!

:O)

Glad you found a solution to your issue in all seriousness. We do realise that people heavily modify the HYPTUSS tools for their own uses, as I do myself, and we do acknowledge that our updates sometimes cause anyone who does this these sorts of problems.

Hopefully the upside is worth it in terms of new features or bug-fixes, although we do try to be careful to let people know just what the improvements are between versions, so people can take a view whether an up-date is worthwhile or not at any particular time.

Cheers,

Itsallaguess

[staffordian]

P.S. It's all Kiloran and Itsallaguess's fault for updating the incredibly useful HYPTUSS and me having to then update my heavily modified version of it

Thanks, I think!

:O)

Glad you found a solution to your issue in all seriousness. We do realise that people heavily modify the HYPTUSS tools for their own uses, as I do myself, and we do acknowledge that our updates sometimes cause anyone who does this these sorts of problems.

Hopefully the upside is worth it in terms of new features or bug-fixes, although we do try to be careful to let people know just what the improvements are between versions, so people can take a view whether an up-date is worthwhile or not at any particular time.

Cheers,

Itsallaguess

I wouldn't be without the spreadsheet.

I currently use the latest version for my B7 of ITs and a veeeeery old one for my HYP.

This old one has all sorts of interconnections and I've never dared try to update it so am missing out on loads of great functionality, but thanks to Slarti's hint about updating the formulae, I'm giving it a go.

I'll probably be back in a month or so when I've either sorted it or torn the rest of my hair out

Staffordian