Increment a formula when copying worksheet?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top