Copy a formula every 7th line

[corppunishment11]

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.

 

[jasonb75]

See if this works

=","&"'"&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]

If you have the SEQUENCE function (currently only in office 365), then an alternative would be

=",'"INDEX(Sheet1!A2:A1000,SEQUENCE(CEILING(ROWS(Sheet1!A2:A1000)/5,5),,1,5))&"'"

Which would fill down dynamically.

 

[corppunishment11]

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?

 

[corppunishment11]

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]

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]

Understood. Here is the actual:

Place	Time	Status	Notes
New York	1:00 PM​	Closed	opportunity no longer valid
Vegas	2:00 PM​	Open	continue to illustrate
California	8:00 AM​	Open	funds coming
Minnesota	6:00 PM​	Closed	opportunity 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]

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
	A	B	C	D	E	F
1	,'New York'		Place	Time	Status	Notes
2	,'1:00 PM'		New York	1:00 PM	Closed	opportunity no longer valid
3	,'Closed'		Vegas	2:00 PM	Open	continue to illustrate
4	,'opportunity no longer valid'		California	8:00 AM	Open	funds coming
5	,'Vegas'		Minnesota	6:00 PM	Closed	opportunity 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
Range	Formula
A1:A18	A1	=",'"&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]

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]

To simply remove the time logic from the formula, still working with 4 continuous columns

=",'"&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.