excel help please!!

Hope someone can help with this one...

I'm co-ordinating the company stocktake this weekend. I have two excel files, one called "Suppliers Master" and one called "Stocktake Master". The stocktake master file has each department's stock quantities listed, and the price in each worksheet is linked to the Suppliers Master file. Next week, I'll get all the paperwork back from the departments and have to manually enter the stock quantities in their worksheet.

I want to make sure I "freeze" the prices in each stocktake worksheet at this weekend's prices, but will need to carry on working with new supplier invoices before the accountants have finished with the Stocktake file.

I wondered if I was to create a copy of Stocktake Master, would the new file just have the prices in and not the links? I tried it at home with a quick mock-up, but we run Open Office, not Excel, so I'm not sure if Excel will carry the link over.

Thanks for any help!


  • Mr PuffyMr Puffy ✭✭✭

    If you copy it then the links will copy too.  If you don't want the links, only the values, then copy and paste special values back into the cells concerned.

    We are stocktaking too image

  • Does that mean all you'll be doing is 'Saving as' Stocktake Master with a new file name? If so, I'm sure it'll be fine. 

  • Thanks...but have you both given me different answers? image

    I hate the stocktake...normally it's been annual, and the last time, I'd not done the linking between the spreadsheets. Now the accountants have demanded a six monthly one...and rumour has it we'll be going quarterly soon too.

    I might book leave for then!

  • Use paste special and paste the values only.  That should remove the link to the other spreadsheet
  • Mr PuffyMr Puffy ✭✭✭
    I'm not sure  whether you want to make a copy of your file with the same links as the existing file, or make a copy of it with no links, just the prices as they appear on screen?
  • Looking at the other two answers, maybe I misinterpreted what you wanted. I thought you wanted to keep the links between the sheets, and not lose them.
  • Sorry, LN, I want to remove the link in the new version...I think...

    Thanks for the suggestions, I'll have a go at copying the values only on one page and see where that gets me.

  • Okay. In that case, what Mr Puffy and Gertie said image

  • Mr PuffyMr Puffy ✭✭✭
    I know this is sad, but has it worked?
  • Very sad, but I won't know properly until I get back to work on Monday. I'll let you know. image
  • *sits at home on tenterhooks*
  • Are you ready for the breaking news?

    I made a copy of the master Stocktake file, saved it and renamed it. Click on a cell which contains a link to the Master suppliers file. Then, click on "edit" on the drop down menu, select "link", then <here's the good bit> Select the option which says "break link".

    It breaks all the links, not just the cell you clicked in.

    Thanks for all the suggestions!

Sign In or Register to comment.