Excel - Replace offset formula with actual cell reference

3
votes

I have a table that is pulling thousands of rows of data from a very large sheet. Some of the columns in the table are getting their data from every 5th row on that large sheet. In order to speed up the process of creating the cell references, I used an OFFSET formula to grab a cell from every 5th row:

=OFFSET('Large Sheet'!B$2572,(ROW(1:1)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(2:2)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(3:3)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(4:4)-1)*5,,)
=OFFSET('Large Sheet'!B$2572,(ROW(5:5)-1)*5,,)
etc...

OFFSET can eat up resources during calculation of large tables though, and I'm looking for a way to speed up/simplify my formula. Is there any easy way to convert the OFFSET formula into just a simple cell reference like:

='Large Sheet'!B2572
='Large Sheet'!B2577
='Large Sheet'!B2582
='Large Sheet'!B2587
='Large Sheet'!B2592
etc...

I can't just paste values either. This needs to be an active reference, because the large sheet will change.

Thanks for your help.

3
excelexcel-formulavba
The answer below might be a little better with speed. If you want pure references, you can do it quickly with VBA. – Byron Wall
Thanks Byron. A non-volatile method should help. Hopefully I'll have some time to check it later today and will comment back. I was just now thinking that this is slowing down a macro I'm running, but I could also potentially turn off calculation during the macro until the macro needs the table data. I'm going to try both and see if either help. – Ryan Kok

3 Answers

2
votes

And here is one last approach to this that does not use VBA or formulas. It's just a quick and dirty use of AutoFilter and deleting rows.

Main idea

  • Add a reference to a cell =Sheet1!A1 and copy it down to match as many rows as there are in the main data.
  • Add another formula in B1 to be =MOD(ROW(), 5)
  • Filter column B and uncheck the 0s (or any single number)
  • Delete all the rows that are visible
  • Delete column B
  • Voila, formulas for every 5th row

Some reference images, these are all taken on Sheet2.

Formulas with AutoFilter ready.

formulas with filter

Filtered and ready to delete

filtered

Delete all those rows (select A1, CTRL+SHIFT+DOWN ARROW, SHIFT+SPACE, CTRL+MINUS)

delete rows

Delete column B to get final result with "pure" formulas every 5th row.

result

2
votes

If you want to take a VBA approach to this, you can generate the references very quickly using simple For loops.

Here is some very crude code which can get you started. It uses hard-coded sheet names and variables. I am really just trying to show the i*5 part.

Sub CreateReferences()

    For i = 0 To 12
        For j = 0 To 5
            Sheet2.Range("H1").Offset(i, j).Formula = _
                "=Sheet1!" & Sheet1.Range("A5").Offset(i * 5, j).Address
        Next
    Next

End Sub

It works by building a quick formula using the Address from a reference to a cell on Sheet1. The only key here is have one index count cells in the "summary" rows and multiply by 5 to get the reference to the "master" sheet. I am starting at A5 just to match the results from INDEX.

Results show the formula input for H1 and over. I am comparing to the INDEX results generated above.

results

0
votes

Here is one approach using INDEX instead of OFFSET. I am not sure if it is faster, I guess you can check. INDEX is not volatile, so you might get some advantage from that.

Picture of ranges, you can see that Sheet1 has a lot of data and Sheet2 is pulling every 5th row from that sheet. The data in Sheet1 goes from A1:F1000 and just reports the address of the current cell.

sheets

Formulas use INDEX and are copied down and across from A1 on Sheet2.

=INDEX(Sheet1!$A$1:$F$1000,ROW()*5,COLUMN())