Clever Geek Handbook

Excel - replace offset formula with actual cell reference

I have a table that pulls thousands of rows of data from a very large sheet. Some of the columns in the table get their data from every fifth row on this large sheet. To speed up the process of creating cell references, I used the OFFSET formula to grab a cell from every fifth 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 when calculating large tables though, and I'm looking for a way to speed up / simplify the formula. Is there an easy way to convert the OFFSET formula to a simple cell assignment like:

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

I cannot just insert values. This should be an active link because the big sheet will change.

Thank you for your help.

+3


source to share


3 answers


And here's a final approach to this that doesn't use VBA or formulas. It's just a quick and dirty way to use AutoFilter and delete lines.

main idea

  • Add a cell reference =Sheet1!A1

    and copy it to align as many rows as in the master data.
  • Add another formula in B1

    like=MOD(ROW(), 5)

  • Filter column B

    and uncheck 0s (or any single number)
  • Remove all visible lines
  • Delete column B
  • Voila, formulas for every fifth line

Some reference pictures , all taken from Sheet2

.

Formulas with autofilter are ready.

formulas with filter



Filtered and ready to be removed

filtered

Delete all these lines (select A1

, CTRL + SHIFT + DOWN ARROW, SHIFT + SPACE, CTRL + MINUS)

delete rows

Delete column B to get the final result with "clean" formulas every fifth row.

result

+1


source


If you want to take a VBA approach to this, you can quickly generate links using simple loops For

.

Here is some very rough code to get you started. It uses hardcoded names and variables. I'm just trying to show a part i*5

.

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 creating a quick formula using Address

from cell reference to Sheet1

. The only clue here is to have the index count cells in the "summary" rows and multiply by 5 to get a reference to the "master" sheet. I start with A5

just to match the results INDEX

.

The results show you entering a formula for H1

and more. I am comparing the results INDEX

obtained above.

results

+2


source


Here's one approach using INDEX

instead OFFSET

. I'm not sure if this is faster, I think you can check. INDEX

unstable, so you can take advantage of it.

Picture of the ranges , you can see that it Sheet1

has a lot of data and it Sheet2

pulls every fifth row from this sheet. The data in Sheet1

goes from A1:F1000

and simply reports the address of the current cell.

sheets

Use formulasINDEX

and copy down and across from A1

on Sheet2

.

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


source






More articles:


All Articles