I want to add a sheet into a workbook which will have a load of lookup formulae to other sheets in that workbook. Thing is, the workbook is updated by a number of people, and they could do without me locking it out for a good few hours to do all my messing about.

I was thinking of creating the sheet I want in another workbook, with the lookup links back to the original, and then when I’m happy it’s all how I want it I’ll copy the new sheet whole back into the original workbook. Will that work?

And any fancy multiuser parallel update options, via Office365/SharePoint etc, to the original sheet are not available. The client site is on v.old Sharepoint, so it’s check it out, update, save, check it in again, that’s it.

Shouldn’t be a big problem – create your sheet referencing the original then stick it back in when you’re done. I’d expect Excel will drop the workbook from any formula references when you do that but if it doesn’t you can just do a global replace in all the formulae on the sheet you’ve just stuck in.

Edit – just checked and it’s as expected. Create your new sheet, once it’s done do the right click on sheet name at bottom, ‘move or copy’, choose the workbook you’re putting it in and all the file references get stripped. Named ranges and stuff can cause some warnings to appear but if it’s a bog standard sheet with formulas and stuff then it’ll be fine.

can you not test it by making a copy of the original workbook on Sharepoint?

Ta. Just had a bit of an experiment and it seems to work fine.