Find and Replace

[smods]

Hello guys,

Situation is that I have a spreadsheet which has 6 sections split up by page breaks and acts like big sum for 10 or so products. The order is:

Stock left over from previous week* +
Stock delivered this week -
Stock Adjustments =
Total Stock Available for sale for that week -
Total Stock sold this week =
Total Stock left over for next week

*The values here look at the total stock left over from the previous week on a different worksheet.

I have a worksheet for each week. When I create a new week I right click on the tab and copy it, rename the tab and then change the dates. I have to find & replace on the "Stock left over from previous week" so it so it's looking at the previous week.

For example Worksheet named 09.06.2008 looks at Stock Leftover on worksheet 02.06.2008 I then copy worksheet 09.06.2008 and rename it to 16.06.2008.

However the Stock Left over from previous week is still looking to 02.06.2008 so I find and replace to change all references to 02.06.2008 to 09.06.2008 - Hope you're following lol

Now I have to hand this of to someone else to do and have protected the worksheet so none of the formulas used can be erased or deleted etc and I've found you cannot find and replace in a protected worksheet. Can you guys think of a way around this or a better solution? Any help would be hugely appreciated

 

[Gerald Higgins]

Sorry, that's kinda the point about protection

What you could perhaps do, is set up a macro that turns protection off, carries out the find and replace, and then turns protection back on.

 

[Gerald Higgins]

Another possible solution might be to leave one section - perhaps a single cell - unprotected, where the user could change the relevant date(s)

 

[smods]

The ten cells that look to the previous week are all unprotected but find and replace still doesn't work! Trying to avoid the user having to change all 10 references individually

 

[Gerald Higgins]

How about creating a new worksheet that is not protected, that ONLY contains these 10 formulas ?

Then, on you main sheet, have formulas that just pick up the result of this new sheet.
Then, you can protect all sheets except the new one, allowing people to do Find and Replace on it

 

[smods]

Ok, can you find and replace in a specific range?? If so can someone give me the code to put on a command button that does:

Unprotects sheet (for the sake of the code the password is excel)
Finds in the range E7:E21 the value that matches that of cell C3
Replaces any matches in this range with the value in D3
Protects the sheet

Is this even possible?

Cheers

Chris