DJ Dunk

iTrader: (5)

You're welcome Mick, glad its sorted

[Edited by DJ Dunk - 11/3/2003 10:31:58 AM]

Mick

iTrader: (1)

I have a large data spreadsheet that I have condensed for another application.

Trouble is some cell references totalling values now have some of the values missing and the formula has inserted a #REF! instead of the cell name.

Is there an easier way of dleting all these useless references to non existant cells than manually running through every formula?

I tried to do it with 'find & replace' but as soon as the formula wizard clocks that the formula is wrong it tries to help by pointing out the fact that the formula doesn't work - can you turn this thing off? - Is there any other way of doing this?

Cheers

Mick

Buckrogers

Quite easy, what you need to do is replace the formula containing the REF# with an IF statement.

IF cell A1 = less than zero do nothing, else do original formula.

(This is not the actually formula, but in laymans terms)

Buckrogers

PS Dont know how to turn the annoying wizards off, I just dont ever install them!

Mick

iTrader: (1)

The function does not seem to be an 'Add In' otherwise I would be able to un-instal it.

The formulae are of the form: =SUM(#REF!,#REF!,#REF!,BM19,#REF!,#REF!,#REF!,#REF !,BM28,#REF!,#REF!,#REF!,#REF!,#REF!) where in this case 12 of the previous values have been deleted but I would still like to keep the values that are still there and sum them.

Mick

DJ Dunk

iTrader: (5)

Try search and replace for #REF!, (with the comma) then search and replace for #REF! . . replace with nothing. Works on mine (Excel 2003)

[Edited by DJ Dunk - 10/31/2003 11:01:34 AM]

Buckrogers

Mick, wizards as you say are not "ad in's". If you go through the setup.exe process, you should be able to add / remove components of Office (excel)

Try DJ Dunks solution, it should work. Failing that, if you wont to copy part of the offending cells in to a new workbook and mail me, I'll have a look for you.

Dream Weaver

Why not just add a new forumla in the adjacent cells and copy it down.

=BM19+BM28, then copy that all the way down the list.

Mick

iTrader: (1)

I saw DJs formula pretty soon after he posted, I replied thanking him greatly and thought that was sorted!
My post now seems to have disappeared.

So... Again...
Dear Mr Dunk
Thankyou very much for your suggestion, it worked perfectly first time and enabled the sheet to be sorted in seconds
- You are a hero

Cheers

Mick