Copy formula changing the row increment

sfinn

New Member
Joined
Jun 8, 2020
Messages
3
Platform
  1. Windows
  2. MacOS
Hi,

I have a spreadsheet that I use as a template so the number of lines will be different within each file meaning that it would be far more efficient for me to copy the formulae down rather than manually amend.

There are 3 rows of formulae that reference data from a second tab on the same row with a blank row below. What I am hoping to do is adapt the current formula so that the 4 rows can be grouped and copied down the sheet with each reference increasing by 2.

I have attached a screenshot of the sheet.

I want to highlight the 4 rows and copy the formulae down so the next 'grouping' would reference row 13. When copied down it changes to 15 (adding 4 onto the previous).

Apologies for the long winded explanation and thanks in advance!
 

Attachments

  • Sheet1.jpg
    Sheet1.jpg
    76.6 KB · Views: 11

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Here's the formula for C3, you will need to create the other 5 in the first 3 rows, changing the INDEX range each time before dragging them down in a group.

=INDEX(Data2!Q:Q,INT((ROW()-3)/4)*2+7)
 
Upvote 0
Here's the formula for C3, you will need to create the other 5 in the first 3 rows, changing the INDEX range each time before dragging them down in a group.

=INDEX(Data2!Q:Q,INT((ROW()-3)/4)*2+7)

Thank you for that Jason that does exactly what I need it to do in column C.

I'm sorry but I'm not sure what you mean with creating the other rows. I've not used the INDEX function before so not too sure where to start..
 
Upvote 0
So for the formula in column B, you would need to use

=INDEX(....)&"Option 1"&INDEX(....)&"m"

Change the range at the start of INDEX each time, e.g Data2!Q:Q to Data2!C:C etc, but don't make any changes to the INT part. I'm a bit pressed for time at the moment, see how you get on, let me know if you get stuck and I'll type the formula out in full when I get back later.
 
Upvote 0
Thanks a lot for that, managed to get it working! Much appreciated (y)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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