Copying a formula that you want to use the same cells but different sheet

[fitz921322]

Basically as the title says. I have a formula that I'm trying to paste to a different cell, and I would like to keep the cells the same but change the sheet. I'm looking for quick way to do this besides having to go in and change the cells and sheets manually. Let me give you an example.

C11 = {=MIN(IF('Week 1'!C3:C11,"0",'Week 1'!B3:B11))}

I then copy and paste to C23 and this is what I get:

C23 = {=MIN(IF('Week 1'!C15:C23,"0",'Week 1'!B15:B23))}

Ideally I would like it to look like this:

C23 = {=MIN(IF('Week 2'!C3:C11,"0",'Week 2'!B3:B11))}

I'm looking for a quick fix because I have 320 formulas similar to this, that I'm going to have to copy 8x each to gather data from 8 separate sheets. That's a lot of tedious work.

Thanks!!

 

[etaf]

use $ to stop the cells changing

{=MIN(IF('Week 1'!$C$3:$C$11,"0",'Week 1'!$B$3:$B$11))}

only way I can think is to use a helper cell , that has the week 1 and week 2 etc in and using indirect to reference

 

[fitz921322]

Thanks! I'm pretty new to the more complicated formulations. That's the first formula that I've had to hit the CTRL+SHIFT+ENTER. Could you use the same formula
{=MIN(IF('Week 1'!$C$3:$C$11,"0",'Week 1'!$B$3:$B$11))} and show what that would look like with an indirect calculated into it. I've tried searching examples but it's a little confusing. Just to reiterate, I would like Week 1 to switch to week 2. Make up your own cell that you've put redirect into.
Thanks again Wayne.

 

[thisoldman]

This is a workaround that I sometimes use to perform similar edits after pasting formulas.

Switch to Formula View by pressing Ctrl+` (that's the backtick, located under the tilde, '~', on my keyboard).
Select the range with the formula or formulas you wish to change.
Open the "Find and Replace" dialog, Ctrl+H is the shortcut.

Find what: ek 1
Replace with: ek 2
​

Click the 'Replace All' button.
Switch back to normal view with Ctrl+`

The above will change all occurrences of "Week 1" with "Week 2" in the selected range.

Caution: if you have a single cell selected and the text to replace is not present or is a single instance, the 'Replace All' will replace the text in every cell in the worksheet. It's safer to select at least two cells, one of which may be a blank cell, if you wish to replace text in just a single cell.

 

[etaf]

you should be able to use

{=MIN(IF(indirect("'Week "&AA2&"'!$C$3:$C$11","0",indirect("'Week "&AA2&"'!$B$3:$B$11"))}

I have used cell AA2 - you can change that to any cell you want
in AA2 put the number 1
in AA3 put the number 2
in AA3 put the number 3 - in excel you should be able to drag down

now the week will be changed to the number that is in Cell AA2 etc

we may also be able to use ROW() to do that as well

see how the above works out for you

 

[fitz921322]

Thanks thisoldman and Wayne!!! Adding the $ and using the find and replace function came in very handy. Unfortunately, the indirect was a little too complicated. Thanks again, by combining both of your feedback my sheet is completed and is the most gratifying excel books I've created. Keep up the good work!