# Increment a formula when copying worksheet?

#### slam

##### Well-known Member
I have a worksheet named R1

I need to copy this worksheet and create R2 through R40

There are several formulas on this worksheet I'd like to change, so that they automatically increment before copying the worksheet

For instance, I have this formula =Setup!K2&" Results" on the worksheet R1

On the worksheet R2, I would want that formula to increment to =Setup!K3&" Results"

Is there some type of INDIRECT function that would do this automatically? There's 6 formulas I could apply this to, so that's 6 x 40 = 240 formulas I'd have to manually change.... and I'd rather not if there's a way to automate it!

Thanks!

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### oldbrewer

##### Well-known Member
on R1 you want to use K2
starting from A1 K2 is =offset(\$a\$1,1,10)

if you have a helper area the 1 and the 10 - - - THEN - - - 2,10 could come from there

on sheet R2 Z1 could be 2 and Z2 could be 10

40 sheets but 5 minutes to plug values into Z1 and Z2

#### slam

##### Well-known Member
on R1 you want to use K2
starting from A1 K2 is =offset(\$a\$1,1,10)

if you have a helper area the 1 and the 10 - - - THEN - - - 2,10 could come from there

on sheet R2 Z1 could be 2 and Z2 could be 10

40 sheets but 5 minutes to plug values into Z1 and Z2

I am totally lost, sorry. To be clear, K2 is on the Setup worksheet, and the formula in my first post is in A1 on the worksheet named R1.

I know what you mean about helper columns, I can make those... but what is my formula in A1 now?

#### oldbrewer

##### Well-known Member
say you have 3 sheets and Z1 is 6,7,8 on the three sheets

in A1 on each sheet enter =offset(\$c\$1,1,Z1)

then A1 will be equal to D7, D8 and D9 on the three sheets

so the value in Z1 on any sheet is now driving the formula

#### slam

##### Well-known Member
say you have 3 sheets and Z1 is 6,7,8 on the three sheets

in A1 on each sheet enter =offset(\$c\$1,1,Z1)

then A1 will be equal to D7, D8 and D9 on the three sheets

so the value in Z1 on any sheet is now driving the formula

Still confused. I understand what that is doing, but it doesn't seem to be a solution to my problem. It isn't referencing K2 on my Setup worksheet at all.

#### slam

##### Well-known Member
Well, I have figured out a solution!

I did create helper cells.

To produce the worksheet name, cell Z1 contains this formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Result: R1

Then to equal the row reference on the Setup worksheet, cell Z2 contains this formula:

=(SUBSTITUTE(Z1,LEFT(Z1,1),""))+1
Result: 2

Then A1, to replace my formula from the original post is:

=INDIRECT("Setup!K"&Z2)&" Results"

I can now copy this worksheet to R2 through R40, and the reference to Setup cells increments automatically!

Replies
1
Views
97
Replies
10
Views
268
Replies
0
Views
217
Replies
1
Views
98
Replies
3
Views
454

1,195,712
Messages
6,011,259
Members
441,598
Latest member
chrispaulpearce

### 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