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]

That did it! Thank you again!!!

 

[corppunishment11]

What if I was needing to replace the time column with a date? 00/00/0000

 

[jasonb75]

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]

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]

Something like this should do it.

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

 

[corppunishment11]

Thank you!

 

[corppunishment11]

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]

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]

What I need is to have the blanks come back as NULL with no parentheses.

FirstName

LastName

Email

CellPhone

WorkPhone

Account

Address

City

State

Zip

RVP

ROLE

Market

Title

ContactCreateDate

ContactLastUpdateDate

MarketCode

extension

Ranking

Solicitation

HolGift

ID

INDSGC

('Oscar'

Oscar

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

3/31/2021​

Big & Small List

YES

YES

KEMFRO

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]

See if this fixes it. The text columns should stay blank anyway, this should blank out any empty date cells.

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