Another spreadsheet query

[Siouxsie]

I have inherited a spreadsheet which has 5 worksheets with identical colums / rows.

The first page is basically a sum of the numbers entered on sheets 2 - 5 in that same cell ie if c3 on sheets 2,3,4 & 5 is the amount of baked beans sold by those 4 individual salesmen then c3 on sheet 1 is the total sales.

However as we no longer have salesman 2 I want to delete this whole sheet. If I just delete it all the cells on the summary sheet 1 show an error. How can I do this without having to re-write all the formulae?

I'm sure there must be an easy way, so any help would be appreciated.

 

[Dickovforengland!]

If you highlight the summary sheet then Edit, Replace to bring up the Find & Replace window. Replace "Sheet2!*," with nothing that should sort it.

 

[Darth Vodka]

I have inherited a spreadsheet which has 5 worksheets with identical colums / rows.

The first page is basically a sum of the numbers entered on sheets 2 - 5 in that same cell ie if c3 on sheets 2,3,4 & 5 is the amount of baked beans sold by those 4 individual salesmen then c3 on sheet 1 is the total sales.

However as we no longer have salesman 2 I want to delete this whole sheet. If I just delete it all the cells on the summary sheet 1 show an error. How can I do this without having to re-write all the formulae?

I'm sure there must be an easy way, so any help would be appreciated.

i take it your formula is =sheet1!c3+sheet2!c3 + sheet3!c3 etc ?

you could use a 3d formula like
=SUM(Sheet1:Sheet3!C3)

?

 

[Siouxsie]

i take it your formula is =sheet1!c3+sheet2!c3 + sheet3!c3 etc ?

you could use a 3d formula like
=SUM(Sheet1:Sheet3!C3)

?

yep it's currently written like that. Your way would have made life easier.

 

[Siouxsie]

If you highlight the summary sheet then Edit, Replace to bring up the Find & Replace window. Replace "Sheet2!*," with nothing that should sort it.

That looks like it should work but I can't get it to currently. I'll keep playing. Thanks.

 

[Darth Vodka]

yep it's currently written like that. Your way would have made life easier.

is it that you are loathe to change lots of formulae?

DFE's method of find and replace will work, but watch out for exclamation marks and sheets with spaces in their names

really you shouldn't have done one sheet per sales person, have it all on one sheet

 

[Dickovforengland!]

That looks like it should work but I can't get it to currently. I'll keep playing. Thanks.

Paste your formula in here if you can't sort it but like DV said, 3d formula is the way to go or re-structure of the spreadsheet. Otherwise every time you hire or fire a Salesman you'll be messing about with formulae.

 

[Siouxsie]

is it that you are loathe to change lots of formulae?

DFE's method of find and replace will work, but watch out for exclamation marks and sheets with spaces in their names

really you shouldn't have done one sheet per sales person, have it all on one sheet

As I said I inherited the spreadsheet, it isn't actually all about salesmen that was just an example and the reason I am loathe to change the formulae is that it is hundreds of lines long.

I'm semi tempted to scrap it all redo it properly.

Thanks everyone for the help.

 

[Matt_B]

Wimmin on computers, whatever next

 

[Darth Vodka]

I'm semi tempted to scrap it all redo it properly.

always rewrite it

you learn more about excel and the data

 

[Steven]

Wimmin on computers, whatever next

http://news.bbc.co.uk/1/hi/uk/7270307.stm :icon_conf

 

[Siouxsie]

Wimmin on computers, whatever next

:icon_cry:
listen - when I did my "O" Level computer studies it was using paper tapes, punch cards and was mostly theory. We had one terminal that connected to the Leics County Hall mainframe over night so we wrote out programs using Basic and sent it down the wire and you got the result the following morning!

As for spreadsheets it was lotus 123 all the way.

 

[Matt_B]

http://news.bbc.co.uk/1/hi/uk/7270307.stm :icon_conf

Well that was to be expected:

"Question, how do you create a new word document?"

Girl answer: "Click on the Word icon, click file and then new"
Boy answer: "**** word, check this out www.2girls1cup.com! And did you know there is porn and Call of Duty 4 online!"