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.

 

[corppunishment11]

Thanks. The issue I'm having with this is that it's changing the formatting due to the initial and ending ",'"

Also, it's returning my blank cells with the ",'" when I'm hoping to have it return the word NULL without any quotes and not change the format of the other cells. What do you think?

 

[jasonb75]

Think I follow now, not 100% sure though. See if this one is any better.

=SUBSTITUTE(",'"&TEXT(INDEX(C:F,INT(ROWS(A$1:A1)-1)/4+2,MOD(ROWS(A$1:A1)-1,4)+1),CHOOSE(MOD(ROWS(A$1:A1)-1,4)+1,"@","@","@","dd/mm/yyyy"))&"'",",'0'","NULL")

 

[corppunishment11]

Okay, here's my updated & actual formula based on a number of additions that have been made: This does not return null. It returns ,' ' in the blank cells. Everything else works perfectly. Again, any help is appreciated!!

=SUBSTITUTE(" "&TEXT(INDEX(E:AA,INT(ROWS(E$1:E12)-1)/23+2,MOD(ROWS(E$1:E12)-1,23)+1),CHOOSE(MOD(ROWS(E$1:E12)-1,23)+1,"('@'",",'@'",",'@'",",'@'",",'@'",",'@'",",'@'",",'@'",",'@",",'@'",",'@'",",'@'",",'@'",",'@'",",'mm/dd/yyyy'",",'@'",",'@'",",'@'",",'@'",",'@'",",'@'",",'@'",",'00'')"))&" ",",'0'","NULL")

 

[jasonb75]

The ",0'" part is what is being replaced with "NULL", if you change that to whatever is currently being shown in the cells that should say NULL then it should work.

Based on your post, it looks like it should be ",' '" although I'm unsure if there is actually a space between the single quotes, or if you have just added one in to show that they are 2 single quotes rather than 1 double.

 

[corppunishment11]

I'm obviously doing something wrong. I replaced the ",0'" with "NULL" and removed the second "NULL". It's still returning ,' ' for blank cells.

What am I missing? Thank you!

 

[jasonb75]

Replace ",0'" with whatever is being shown in the cell (as per the example in the second line of my previous post), not replace it with NULL.