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.
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
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?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
Think I follow now, not 100% sure though. See if this one is any better.
Excel Formula:
=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

New Member
Joined
Dec 7, 2018
Messages
49
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2018
Messages
49
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

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