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

#### fitz921322

##### New Member
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!!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

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.

Last edited:
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.

Last edited:
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

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!

Replies
2
Views
249
Replies
7
Views
360
Replies
6
Views
293
Replies
1
Views
137
Replies
11
Views
371

1,196,309
Messages
6,014,586
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back