Magento Question posted in
The official documentation can be found here.

How to Keep Excel Cells in a Formula Anchored After Inserting New Rows – Magento

We have a data warehouse on RJ Metrics (Magento BI Tool) that we are trying to clean up using Excel by building a data dictionary, I’ve created a sheet with instructions and formulas that takes the jumbled data we copy pasted from RJ metrics and sorts it out.

After copying the columns from RJ Metrics, we dump them into an Excel sheet to align them to the correct columns. We have an index formula that reports every Nth cell because when the data is dumped into Excel its turns into one big column. The index formula is supposed to help sort out the data according to their specific columns. However, we encountered a problem as the anchored cells in formula are shifted down when we enter new blank rows to adjust the indexing (in another sheet).

We understand that we can fix this issue just by changing the cell numbers referenced in the index formula every time we paste new data, however there are 12 formulas and we want to avoid wasting time.

Here is a sample of our formula

=INDEX('START HERE'!$D$15:$D$17660,(ROW()-2)*4+4)

We want $D$15 to stay put regardless whether we delete or add rows.

Tags:

2

Answers


  1. 0

    A simple change is where you would put ‘START HERE’!$D$15:$D$17660, put INDIRECT(“‘START HERE’!D15:D17660”) instead. The “‘START HERE’!D15:D17660” is a text value so won’t change. Indirect says to use the text “‘START HERE’!D15:D17660” as a reference.

    =INDEX(INDIRECT("'START HERE'!$D$15:$D$17660"),(ROW()-2)*4+4)

    However, INDIRECT is a volatile function and greatly increases the amount of processing in a workbook, forcing everything to recalculate with every little change.

    Login or Signup to reply.
  2. 0

    I’m not sure if this is too simple-minded, but how about

    =INDEX('Start here'!D:D,(ROW()-2)*4+18)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search