CODEWITHSUNDEEP
excelexcel-formula
CDO
CDO

Reputation: 352

Formula autofill manipulation

I am trying to create a large spreadsheet(10,000 rows of formulas) that takes information in from two other sheets. The basic layout that I want is:

Row1
=Sheet1!A7
=Sheet2!M7
=Sheet1!A8
=Sheet2!M8
=Sheet1!A9
=Sheet2!M9
...etc.

When I try to use to formula auto fill, excel picks up on the wrong pattern and I end up with something like this:

=Sheet1!A7
=Sheet2!M7
...
=Sheet1!A11
=Sheet2!M11
=Sheet1!A17
=Sheet2!M17

I gave excel 10 cells to base the pattern off of, and have not been able to get it to work. Can anyone help me figure out how to do this (hopefully without VBA)?

Upvotes: 1

Views: 613

Answers (5)

Scott Craner
Scott Craner

Reputation: 152505

Try in row 2:

=INDIRECT("Sheet1!A" & 7 +QUOTIENT(ROW()-2,2))

And in row 3:

=INDIRECT("Sheet2!M" & 7 +QUOTIENT(ROW()-2,2))

Highlight both and copy down.

Upvotes: 1

user4039065
user4039065

Reputation:

Try to avoid the volatile¹ OFFSET function or INDIRECT / ADDRESS function pairs in favor of the INDEX function and a little maths.

In the first two cells use these two formulas.

=INDEX(Sheet1!A:A, 7+ROW(1:1)/2)
=INDEX(Sheet2!M:M, 7+ROW(1:1)/2)

Select the two cells and drag the Fill Handle down.

¹ Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.

Upvotes: 4

nwhaught
nwhaught

Reputation: 1592

Use the INDIRECT function to build this, and you can make the pattern work based on your current row number. Assuming your first cell is in Row 2:

=INDIRECT("Sheet1!A" & 7+(ROUNDDOWN(ROW()/2,0)-1))

=INDIRECT("Sheet2!M" & 7+(ROUNDDOWN(ROW()/2,0)-1))

ROW() returns the current row, which is then divided by 2 (since you only increase one row reference for every two cells), and then subtracted 1 (since we want the first adjustment to be 0).

Upvotes: 1

JasonAizkalns
JasonAizkalns

Reputation: 20463

You can solve this without VBA with some =INDIRECT trickery -- the following is located in "Sheet3":

Excel Screenshot

You can type out Sheet1!A and Sheet2!M in cells A1 and A2 respectively and fill down. Then, type a 7 in B1 and the formula in B2 -- again fill down. This first formula is effectively incrementing the count by two. Finally, you can type the formula in C1 and fill down.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is a simple VBA macro that sets up your links. Use it until a non-VBA solution is presented:

Sub propagator()
   Dim i As Long, k As Long
   k = 1

   For i = 7 To 99
      Cells(k, 1).Formula = "=Sheet1!A" & i
      k = k + 1
      Cells(k, 1).Formula = "=Sheet2!M" & i
      k = k + 1
   Next i
End Sub

Just select the destination worksheet and run the macro.

Adjust the 99 to meet your needs.

Upvotes: 2

Related Questions