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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
Using the formula from post 8, you would need to change the time format part, "hh:mm AM/PM" with the required date format, for example "dd/mm/yyyy"
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
Got it. Thank you. One last thing, the date that we've added is now in column F and there is a general number now in row D. How can I get the format to only apply to the dates in F?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Something like this should do it.
Excel Formula:
=",'"&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"))&"'"
Note that there are 4 formats, one for each column. With this method the columns with text also need a format to be specified otherwise there will be #VALUE! errors.
The @ symbol denotes text, anything numeric will be treated as unformatted, the same as the earlier formula without the TEXT() function.
 
Solution

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49

ADVERTISEMENT

Follow up question: What if I need the 7th '@'" to return the word null if it's left blank and I need the null to not have the parentheses..... Not sure about this.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
I'm not sure without a visual example, the current formula is only set up for a 4 element array. I notice that the original thread title was for 7 lines, but everything in the thread seems to be for less.
 

corppunishment11

New Member
Joined
Dec 7, 2018
Messages
49
What I need is to have the blanks come back as NULL with no parentheses.

FirstNameLastNameEmailCellPhoneWorkPhoneAccountAddressCityStateZipRVPROLEMarketTitleContactCreateDateContactLastUpdateDateMarketCodeextensionRankingSolicitationHolGiftIDINDSGC
('Oscar'OscarGrouchOscarGrouch@sesame.com123-456-7890123-456-7890Come On Down To SS123 Sesame StNYNY12345ChrisPuppeteerPBSPresident And Managing Partner
3/31/2021​
Big & Small ListYESYESKEMFRO
000​
,'Grouch'
,'OscarGrouch@sesame.com'
,'123-456-7890'
,'123-456-7890'
,'Come On Down To SS'
,'123 Sesame St'
,'NY'
,'NY
12345
,'Chris'
,'Puppeteer'
,'PBS'
,'President And Managing Partner'
,'03/31/2021'
0
,'Big & Small List'
0
0
,'YES'
,'YES'
,'KEMFRO'
,'00'')
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
See if this fixes it. The text columns should stay blank anyway, this should blank out any empty date cells.

Excel Formula:
=",'"&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,"@","@","@","[>0]dd/mm/yyyy;"))&"'"
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
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