Copy & pasting formula skipping column

dono2209

New Member
Joined
Sep 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to get a formula that will help me with the following. Starting in C1:SF1 is a code (this my data). Starting in C5 I would like to return the value from C1. I would like to skip D5 and in E5 return the value of D1. Next would be G5 returns the value of E1 and repeat the pattern in row 5 until all the values from row 1 are returned to every other cell. Screen shot of example was uploaded
 

Attachments

  • Screenshot 2021-09-14 130200.png
    Screenshot 2021-09-14 130200.png
    4.4 KB · Views: 47

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to the Board!

Here is one way. Place this formula in cell C5 and copy across the row as far as you need to go:
Excel Formula:
=IF(ISODD(COLUMN()),OFFSET($C$1,0,INT(COLUMN()-3)/2),"")
 
Upvote 0
Solution
Hi & welcome to MrExcel.
How about in C5 dragged right
Excel Formula:
=IF(MOD(COLUMNS($C5:C5),2)=0,"",INDEX($C$1:$SF$1,INT(COLUMNS($C5:C5)/2)+1))
 
Upvote 0
Another option if you want a formula that will spill
Excel Formula:
=LET(Seq,SEQUENCE(,COLUMNS(C1:SF1)*2-1),IF(ISEVEN(Seq),"",INDEX($C$1:$SF$1,INT(Seq/2)+1)))
 
Upvote 0
=IF(MOD(COLUMNS($C5:C5),2)=0,"",INDEX($C$1:$SF$1,INT(COLUMNS($C5:C5)/2)+1))
Fluff,
Would it be correct to say that this function you posted is more efficient than mine, as OFFSET is a volatile function and none of the functions you used in your solution are?
 
Upvote 0
Hello and thank you all for the solutions to my problem. I tried Joe4 solution and it solved my problem. Now I will take some time to understand how this worked. First time posting y'all are awesome!
 
Upvote 0
dono2209,

Glad we were able to help. If you have any specific questions about any of the solutions, please feel free to ask them there.

Also note that while my answer does work, Fluff's is a bit more efficient. This is because OFFSET is a volatile function.
That means any time a value is changed on the sheet, my formulas will recalculate (even if it is not necessary to do so).
So it may be a little slower/less efficient than Fluff's. This becomes more noticeable the more of these formulas you have on the sheet.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top