Pasting two formulas and using drag to increment

[Techgique]

Hi all,

I've had some trouble figuring out what I had incorrectly assumed was an easy problem. I've built an excel document that I'm pretty proud of, but have reached a stopping point trying to fill two side-by-side formulas from different sheets to perform some calculations. I am on Excel 2019 and do not have the ability to access VBA functionality (work lockdown policy), so I am hoping there is a formulaic way to drag alternating formulas that increment by one every other cell. When I try the side-by-side copy, it increments (as expected) by 2 for each formula because it is skipping a spot. Is there a way to address this? I included an image for what I would like to do for a more basic approach that I could apply from other sheets, but for now trying to make it easy. Note that there are a lot of values to paste in, so manually entering would be a nightmare. To put it another way, I'm trying to sequentially pull values across a single row on one sheet and then sequentially pull values from a single row on another sheet, then have those show up alternating across a single row. Thank you for any help you are able to offer!

 

[Peter_SSs]

Were those image taken from your Excel 365 or Excel 2019?

I thought that your aim was to have the original data alternating. Why do you have/want these values duplicated?

 

[Techgique]

Hi Peter,

You are correct, and in my haste, I forgot to include what made this work - scooting it one column to the right. I am hoping that someone better versed in these formulas can explain why, but that was what I needed to do in order to get the desired behavior. Updated the image showing the correct drag and output. Thanks!

 

[Peter_SSs]

Thanks for the update. You didn't answer my question about what version the previous images were produced from but I am presuming Excel 2019 not 365.

Some comments about your formula

• Although it works (starting in column B) it doesn't actually need the FLOOR or the +0.05. This should produce the same results. At least it does for me.
  
  =INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A$1:$E1,Sheet1!$A10:$E10),COLUMN()/2)
  
  
• I would be wary about using any formula with the function COLUMN() in it for this job. If anybody subsequently decides to insert any new columns at the left of the sheet, the formulas will no longer return the correct results. Although the formula below is a little longer, it is much more robust in that regard. Further this formula can be placed in column A as I think you originally wanted, and dragged across. Any subsequent column insertions at the left will not 'break' the results.
  
  =INDEX(IF(MOD(COLUMNS($A:A),2)=1,Sheet1!$A$1:$E1,Sheet1!$A10:$E10),(COLUMNS($A:A)+1)/2)
  

 

[Techgique]

Thanks for the update. You didn't answer my question about what version the previous images were produced from but I am presuming Excel 2019 not 365.

Some comments about your formula

• Although it works (starting in column B) it doesn't actually need the FLOOR or the +0.05. This should produce the same results. At least it does for me.
  
  =INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A$1:$E1,Sheet1!$A10:$E10),COLUMN()/2)
  
  
• I would be wary about using any formula with the function COLUMN() in it for this job. If anybody subsequently decides to insert any new columns at the left of the sheet, the formulas will no longer return the correct results. Although the formula below is a little longer, it is much more robust in that regard. Further this formula can be placed in column A as I think you originally wanted, and dragged across. Any subsequent column insertions at the left will not 'break' the results.
  
  =INDEX(IF(MOD(COLUMNS($A:A),2)=1,Sheet1!$A$1:$E1,Sheet1!$A10:$E10),(COLUMNS($A:A)+1)/2)

Peter, so sorry for missing the question. I am using the online 365 version of Excel. Thank you for the tips and I will keep those in mind for any future iterations, luckily this will be a locked document that performs calculations on pasted data, so there (shouldn't) be any interference. I appreciate the help