How to remove #ref

Question

Hi Friends,

I am using an excel file where lots of city wise sheets & one consolidated (summary) sheet in which we have put sum function for summing the amounts from all the sheets. Now I need to delete some sheets (cities) from the file, but it is showing error (#REF) in summary sheet.

The data is huge & removing all the #ref using "Replace function" is not working as when I do so some parts left back i.e. (actual Formula - "=Bangalore!B3+Delhi!B3+...............Mumbai!B3", after removing sheet (Mumbai) it shows "=Bangalore!B3+Delhi!B3+#REF!B3", when replaced "+#REF!", "B3" remains) as I mentioned above the data is huge & it shows (B3, B4, B5..........B500).

Hope there is some way of resolving the issue. Please help...

Thanks,

Hari Singh

Answer 1

Instead of deleting sheets, MOVE  sheets to be deleted to a new workbook.

In the moved sheet workbook select all the sheets to be deleted and press CTRL+A and now press delete button. Don't delete the sheets.

Now in your original file, when you go to Data/Edit link you will find links to the file where sheets have been moved. select file there and break links. 

You will not find any reference error now.

Please note to make your file more automated and efficient working you should change your original formula (=Bangalore!B3+Delhi!B3+...............Mumbai!B3)  to

=SUM(Start:End!B3)

where Start and End are the first and last sheets. There may be any number of sheets in between and now even you delete a sheet you will not get #ref errors.

Answer 2

Thanks for your feedback.

Regarding some other links which are broken but still needed, so you must move those range for which you don't want to break link to a other sheet and this sheet should not be moved.

You have to move only sheet/range which you don't want to have linked in your original sheet.

Hope it helps.

Answer 3

Dear Vijay,

Thanks for your time & the solution you have shared.

There is some issue while breaking the link. As while breaking the links, all the other links (required cities to Summary sheet) also gets broken which we needed.

Also, thanks again for your "=SUM(Start:End!B3)" suggestion, it is very good, but in my case I am unable to use this as my data is not same in all sheets.

Please help if any other solution is there.

Thanks,

Hari Singh