Copy a formula every 7th line

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
I have a spreadsheet where I need to pull in formatted data from every row on a different tab. there are 4 rows of data. The data has to be in rows not columns.

I need to be able to copy that formula with formatting down for over 1,000 rows but Excel obviously doesn't know to keep adding 5 rows to my paste.

Example:
Tab 1:
Headers are Place, Time, Status, Notes
these each have a entry below them in the corresponding cell.

Tab 2:
I have to format the entry from tab 1 as follows: =","&"'"&Sheet1!A2&"'"
I then need to do that in rows for the 4 headers on tab 1.

The issue is that, when I want to paste the formulas, rather than the paste knowing that it needs to grab A2 from sheet 1, it adds 4 and grabs A5 and so on. How do I get it to grab the next row from sheet 1?

I can't imagine that I have to manually write the formula for each cell and each row over 1,000 times.

Thanks.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
See if this works
Excel Formula:
=","&"'"&INDEX(Sheet1!A:A,(ROWS(A$1:A1)-1)*5+2)&"'"
To prevent errors when inserting / deleting rows, the part, ROWS(A$1:A1) should refer to the cell that holds the first formula before you copy it down, so if the formula is going into C10 then you would change it to ROWS(C$10:C10), keeping the single $ symbol in the same place. As you fill down the second part will expand but the first part will remain unchanged.

The next part, *5+2 is the pattern of the range, every 5 rows, starting with row 2. -1 in brackets is a correction factor that should never be changed (without it, the first result will be missed).
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
If you have the SEQUENCE function (currently only in office 365), then an alternative would be
Excel Formula:
=",'"INDEX(Sheet1!A2:A1000,SEQUENCE(CEILING(ROWS(Sheet1!A2:A1000)/5,5),,1,5))&"'"
Which would fill down dynamically.
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
Thanks. So, I can just place my mandated formatting after this sequence? I see the formatting in your first formula. I did use that and it brought back the desired data from A2 in Sheet 1. however, when I copy down, it's just returning ," rather than the data in A3 on Sheet 1

Thoughts?
 
Last edited:

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49

ADVERTISEMENT

Thanks. So, I can just place my mandated formatting after this sequence? I see the formatting in your first formula. I did use that and it brought back the desired data from A2 in Sheet 1. however, when I copy down, it's just returning ," rather than the data in A3 on Sheet 1

Thoughts?
I just noticed I can copy it across columns but I cannot copy down rows. I need the data to stay in the A row all the way down not going across. Not sure what to do here.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
As far as I can see, the formula should do as expected. Please bear in mind that you have only provided a description, which can be open to misinterpretation. If you could provide a small visual example (before and after) it would help to clarify what is required.
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49

ADVERTISEMENT

Understood. Here is the actual:
PlaceTimeStatusNotes
New York
1:00 PM​
Closedopportunity no longer valid
Vegas
2:00 PM​
Opencontinue to illustrate
California
8:00 AM​
Openfunds coming
Minnesota
6:00 PM​
Closedopportunity no longer valid

,'New York'
,'0.541666666666667'
,'Closed'
,'opportunity no longer valid'
,'New York'(pulling in same row instead of grabbing the next row, Vegas)
,''
,''
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
Thanks, that makes it much clearer. I've done this in a single sheet for posting, you just need to change the first range, C:F to match your actual data source.
Book1
ABCDEF
1,'New York'PlaceTimeStatusNotes
2,'1:00 PM'New York1:00 PMClosedopportunity no longer valid
3,'Closed'Vegas2:00 PMOpencontinue to illustrate
4,'opportunity no longer valid'California8:00 AMOpenfunds coming
5,'Vegas'Minnesota6:00 PMClosedopportunity no longer valid
6,'2:00 PM'
7,'Open'
8,'continue to illustrate'
9,'California'
10,'8:00 AM'
11,'Open'
12,'funds coming'
13,'Minnesota'
14,'6:00 PM'
15,'Closed'
16,'opportunity no longer valid'
17,'12:00 AM'
18,'12:00 AM'
Sheet6
Cell Formulas
RangeFormula
A1:A18A1=",'"&TEXT(INDEX(C:F,INT(ROWS(A$1:A1)-1)/4+2,MOD(ROWS(A$1:A1)-1,4)+1),"h:mm AM/PM")&"'"
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
Thank you very much!!! This is great!

If I remove the time and simply have two more columns with text items, how can i remove the time logic and just include those columns?

Again, awesome!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
To simply remove the time logic from the formula, still working with 4 continuous columns
Excel Formula:
=",'"&INDEX(C:F,INT(ROWS(A$1:A1)-1)/4+2,MOD(ROWS(A$1:A1)-1,4)+1)&"'"
However, if you wanted to take the text from say, columns A,C,D,E (skipping column B) then it is going to be significantly more complex. Without testing, I'm not even sure if it would be possible if you're using a version of excel other than office 365.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,156
Messages
5,629,019
Members
416,359
Latest member
Juena

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
Top