Free Microsoft Excel 2013 Quick Reference

Copying relative formula to every other row?


I'm hoping someone will see this and be able to tell me where I'm going wrong!

I've attached an example xls for info - I need each line item on Sheet 2 to appear twice on Sheet 1, but when I copy the formulae down the relative row skips (nothing for lines 15,17,19 or 20 etc.on Sheet 2)

Can anybody please tell me how to get the formula to repeat every other row without having to amend manually?

Thanks in advance for any assistance,

Tigersprite x

Post your answer or comment

comments powered by Disqus
Is there a quick way to copy a formula to every other row?

Formula will be entered in cell G5 and be copied down to G7,G9...G2500

Any body know how to copy a formula to every third row? Such as to row
2, 5 8, 11 and so on without highlight each of those rows manually.

Any help is appreciated.

thank you.

A formula in sheet 1 c5 = sheet2g50
copy this formula to sheet 1 c10 to read sheet2g51
drag and copy to multiple rows after c10

I have a custom sort macro that I would like to output to every other row.

A1 = 1
A2 = 2
A3 = 3

When i sort the values largest to smallest, is there any way to have the results post to every other row so that

A1 = 3

A3 = 2

A5 = 1

hopefully it's simple.

Thanks all.


Would anyone happen to know how to code this in VBA?
I need to fill this formula down (in the green rows), into every other row.

But with the relative referencing (that I need to stay intact) it is making it difficult to figure out how to fill it down into every other row, and reference the cells accurately for the formula to work.
Have thousands of lines that this needs to go through, it shows how many parts we are supposed to be building in a particular month.

******** ******************** ************************************************************************>Microsoft Excel - VBA Learning Tool.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3C3D3E3F3G3H3B5C5D5E5F5G5H5B7C7D7E7F7G7H7=
ABCDEFGH1 JanFebMarAprMayJunJul2Total Cummulative Build206709103Items to build by month20410214Total Cummulative Build15020253040455Items to build by month1505551056Total Cummulative Build15020253040457Items to build by month150555105Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Here are the formulas that are dragged across to the right in the green rows.

Formula in B3

Formula in B5

Formula in B7

And filled down until there is no more data.


If someone doesn't mind, what I'd like to do is to use loops to copy each value in "Sheet1" into every other row in column B of "Sheet2" starting on row 16. I realize that this is a simple task, but I'd like to know how to do this for the purposes of learning the correct VBA coding.

I would like to copy a range to every other cell in a different worksheet. I'm doing it manually now but there must be a better way.

My code now looks like this:

Sheets(1).Range("A10:B10").Offset(DataOffSet, 0).Cells.Copy
Sheets(3).Range("B17").PasteSpecial xlPasteValues
Sheets(1).Range("A11:B11").Offset(DataOffSet, 0).Cells.Copy
Sheets(3).Range("B19").PasteSpecial xlPasteValues

DataOffSet is a variable that allows the user to pick which set of data they want to copy (i.e. Range A4:L11, A14:L21 etc....). Once the user has picked the range, I need to copy the data into the 3rd sheet, every other cell.


My problem is that I am trying to pull date from every other row in
column K to every other row in column J. For example below is my current

Column J Column K

Desired results

Column J

I have used something like the following before to copy data in every other
row except that this will delete the data that I need to keep in column "J"


Any help or suggestions are much appreciated.


This is one of many standard features of ASAP Utilities, available free at

Vaya con Dios,
Chuck, CABGx3

"Tat" <> wrote in message
> I would like to add a color to every other row in a specified row/column
> range. In addition if a row is inserted I would like it to continue the
> pattern of every other row being colored.

I would like to add a color to every other row in a specified row/column
range. In addition if a row is inserted I would like it to continue the color
pattern of every other row being colored.

I have a formula that I would like to copy to every other cell. I have hundreds of cells to copy this to so it would be quite tedious to do it manually. Is there a quick way to do this? I have attached an example.

In the example, I did it manually until row 6. I would like that to be duplicated for the rest of the worksheet. So I would need the formula that is in row 2, that references row 3 to be copied like that equivalently throughout the worksheet. Thanks!


How to make a formula increment cell numbers every other row?

For exemple:

I have a

fixed row content
variable row content
fixed row content
variable row content

But I could have 0 rows to 1000 rows, the number is not know.

Thanks in advance,

On the attached example there is a button called 'unsettled hedges', click this and it loads a userform. In this example, there are 10 rows of data that want a value entering into the 'returns' textboxes.

If you try to enter a value into all of the 'returns' textboxes and click the 'settle hedges' button then all the values from the textboxes should against the relevant row in column L of the 'unsettled hedges' worksheet and then each of these rows should be copied to the next available row in the 'settled hedges' worksheet.

This isn't happening though, instead, it is copying across every other row from the 'unsettled hedges' worksheet, leaving half of the rows still on the unsettled hedges worksheet.

Does anyone understand what is wrong with my code?


Sample attached.

I am using the following formula in conditional formatting but was wondering if it could be altered to also highlight/shade every other row?


I am using this so that when an entry in made in column A, the row will display a border around each cell. I wanted to see if it was possible to also shade every other row and an entry is made into column A.


Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting.


where: K22:K169 are user entered categories from a drop down list
G22:G169 are user entered dates
F9 is a user entered update date
column R are monetary values with first row being a revenue$ second row margin$

I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.




I am trying to link the cells up in a new workbook the problem is the data I need is setup in a way the formula needs to skip every other row.
For example:

When I drag this the next cell down is "C70" 
However I need the formula to be C71 and so on down to C137

How can you drag a formula yet skip every other row?

Any Idea's?

Thank You, Mike

Hello all

For some unknown reason excel fails to detect the pattern of my formula and when trying to copy the formulas to the other cells gets the references completely wrong. How do I do this? Some of you will send me back a sheet with the problem fixed but I'm more looking on how to learn to do it myself.

Please check the attachment.


I have a report like this with thousands of parts :

FinishedG -qty/case--comp part--comp part--comp part-
SystemA 5 part464 part 2121 part 212
System B 10 part414 part 211 part312
System C 5 part4 part21 part 213

I need to calculate costs for finished goods, so beneath each row I have a vlookup formula and a total formula as well as a total/qty per case formula. How can I add these additional lines with forumlas. I have the macro to add a row every other line, but I'm not sure how to paste the formula under every other line without clearing the skipped line.

FinishedG -qty/case--comp part--comp part--comp part-cost/each
SystemA 5 part464 part 2121 part 212
null =B1 vlookup(XXX,2) Vlookup Vlookup =SUM(C2:E2)/B2
System B 10 part414 part 211 part312
null =B3 vlookup(XXX,2) Vlookup Vlookup =SUM(C4:E4)/B4
System C 5 part4 part21 part 213
null =B5 vlookup(XXX,2) Vlookup Vlookup =SUM(C6:E6)/B6

Hi all...

Can't quite figure out how to do this. I'm looking to create a macro that will delete every other row of a filtered set of data. for example, if these are the filtered rows:

row 21
row 22 delete
row 50
row 51 delete
row 58
row 59 delete.

... and so forth down to the bottom of the filter

I cant seem to record the macro using a relative reference with the filtered data

Appreciate any assistance...thanks


I am creating a worksheet where I have a list of account names in a column. I need to insert the same 10 rows of the same data every other row in this column that contains the account names, so that each account has the same data underneath it. I need a fast way of doing it. Please advise if you know how?

Thank you!!!

I am trying to use the little black box to copy my formula down a column, but the formula needs to increase by every second row instead of every row.

For example, my formula in cell J2 reads: =IF(Pyro1000!O2="PASS",Pyro1000!N2,Pyro5000!N2)
The formula for J3 reads: =IF(Pyro1000!O4="PASS",Pyro1000!N4,Pyro5000!N4)

So when I use the little black box on the bottom left of the cell to fill in the rest of the column, I want it to increase the formula with even numbers.
I want J4 to be =IF(Pyro1000!O6="PASS",Pyro1000!N6,Pyro5000!N6)
J6 to be =IF(Pyro1000!O8="PASS",Pyro1000!N8,Pyro5000!N8)
and so on...

Any ideas?

The formulas in my worksheet need to refer to every OTHER cell in another
sheet. How do I copy this function without manually enterring every other

I have some formulas that I would like to copy into every other cell in a
given column .

Is this possible ?

Thank you in advance.

I'm working on a spreadsheet wherein I need to auto copy the formula from the above cell to the new cell below which is technically a new row.

Attached is a snapshot on what I have in excel using the formula in column "H" =SUM(H2,F3,-G3) to calculate the BALANCE column.

So based on my example, if a new row is inserted in Row 8 the formula in cell H8 should be =SUM(H7,F8,-G8) this should be automatically inserted.

Is there a way I can do this automatically?


No luck finding an answer? You could always try Google.