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!

 

[Techgique]

In case it helps for clarity, the reality is A1:E1 and A2:E2 exist on different sheets and row numbers, just trying to see if this simple example is possible to paste in alternating values and then go from there.

 

[Peter_SSs]

Welcome to the MrExcel board!

It depends what the formulas actually are, but here is a simple example. Enter the A5 & B5 formulas then select A5:B5 and drag the Fill Handle to the right.

22 04 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5
2	a	b	c	d	e
3
4
5	1	a	2	b	3	c	4	d	5	e
Drag Formula

Cell Formulas
Range	Formula
A5,C5,E5,G5,I5	A5	=INDEX($A1:$E1,(COLUMNS($A:A)+1)/2)
B5,D5,F5,H5,J5	B5	=INDEX($A2:$E2,COLUMNS($A:B)/2)

 

[Techgique]

Thank you Peter, I ended up going with a single formula that was able to drag across here using Sheet1 and Sheet2 as examples and the ranges as examples. But it can be tailored to any sheet name and range:

Also note that you can surround it with IFERROR("",[ Formula ]) to remove any REF errors.

Thank you all!

=INDEX(IF(MOD(COLUMN(),2)=0,Junk!$A3:$E3,Junk!$A2:$E2),FLOOR(COLUMN()/2+0.05,1))

 

[Techgique]

My sincere apologies, the Code button didn't seem to do what I expected, and the sheet names I was actually using didn't update. Here is the plain single formula I used for alternating from different locations:

=INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A3:$E3,Sheet2!$A2:$E2),FLOOR(COLUMN()/2+0.05,1))

 

[Peter_SSs]

Here is the plain single formula I used for alternating from different locations:

=INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A3:$E3,Sheet2!$A2:$E2),FLOOR(COLUMN()/2+0.05,1))

What sheet name is that formula on?
What is the first cell that contains that formula?
What other cells have you copied/dragged that to?

 

[Techgique]

What sheet name is that formula on?
What is the first cell that contains that formula?
What other cells have you copied/dragged that to?

Hi Peter,

This is a rough example of the actual sheet names I am using, but for simplicity, the formula would exist on a different Sheet name (Like Sheet3). While it did also work for separate sheet names in the formula (Sheet1 and Sheet2), my data existed on the same sheet, just in different rows, but locked to the same column. So, assuming my data was in A2:BX2 and then the matching data was in A3:BX3 (both on Sheet1, then in Sheet3 I am able to enter this formula into A1 of Sheet3 (or any cell really, A2 if I have column names in A1):

=INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A2:$BX2,Sheet1!$A3:$BX3),FLOOR(COLUMN()/2+0.05,1))

Then I just drag it to the right and it alternates between the two values (A2, A3, B2, B3, A4, B4, etc)

 

[Techgique]

Hi Peter,

This is a rough example of the actual sheet names I am using, but for simplicity, the formula would exist on a different Sheet name (Like Sheet3). While it did also work for separate sheet names in the formula (Sheet1 and Sheet2), my data existed on the same sheet, just in different rows, but locked to the same column. So, assuming my data was in A2:BX2 and then the matching data was in A3:BX3 (both on Sheet1, then in Sheet3 I am able to enter this formula into A1 of Sheet3 (or any cell really, A2 if I have column names in A1):

=INDEX(IF(MOD(COLUMN(),2)=0,Sheet1!$A2:$BX2,Sheet1!$A3:$BX3),FLOOR(COLUMN()/2+0.05,1))

Then I just drag it to the right and it alternates between the two values (A2, A3, B2, B3, A4, B4, etc)

Sorry, but to add, I was also able to drag this down after dragging it across since the data aligned. Oddball use-case, I know, but it's how the data was in the sheet. Imagine names of something across 75 columns and then specific values across 75 columns that match those names, then the same pattern of names and values multiple rows down. This allowed me to quickly (relatively, still had to figure out how to do the formula) pull together those values side-by-side and then repeat the process for each category containing those names and values.

 

[Peter_SSs]

then in Sheet3 I am able to enter this formula into A1 of Sheet3

If I enter it into A1 (or anywhere in column A) of Sheet3 & drag right I get a #SPILL! error.

 

[Techgique]

Hi Peter,

I was excited to share an example file I made for you only to realize I can't upload (I can't install that XL2BB thing on my machine). However, here are the screenshots that should allow you to replicate exactly what I am up to. Sorry for my imperfect Excel syntax explanation, I'm more of a tinkerer, but this should allow you to get functionality with what I did. Sheet 1 is the example collection of data on multiple lines. Sheet 2 (preDrag) has the single formula that can be dragged right and then down, and finally the other Sheet 2 image is the result. Hope this helps!