Forum Discussion

JTaverna's avatar
JTaverna
Copper Contributor
Dec 04, 2019
Solved

Copying complex formulas across columns while incrementing row number references

I am working on an issue that is driving me nuts. I am trying to copy the formula below across many columns. This is a complex spreadsheet where we are referencing data vertically down through many r...
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Dec 04, 2019

    JTaverna 

    You may try something like this...

     

    =IF(ISERROR(INDEX($F:$F,30+(COLUMNS($A1:A1)-1))),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Dec 04, 2019

JTaverna 

You may try something like this...

 

=IF(ISERROR(INDEX($F:$F,30+(COLUMNS($A1:A1)-1))),"",VLOOKUP(H15,$B$29:$F$9999,5,FALSE))

  • JTaverna's avatar
    JTaverna
    Copper Contributor
    Dec 04, 2019

    Subodh_Tiwari_sktneerYES! this has solved the problem.  I am always curious to learn new things and the logic behind this would be helpful.

     

    (INDEX($F:$F,30+(COLUMNS($A1:A1)-1)) replaces ($F30) in my formula

    Can you explain the logic for each step within this new part of the formula? I understand the end result has solved the problem, but not sure this code makes it happen.

     

     

     

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Dec 05, 2019

      JTaverna 

      When you place the formula in the first cell, the index part of the formula is this...

      INDEX($F:$F,30+(COLUMNS($A1:A1)-1))

      In the above formula only COLUMNS($A1:A1)-1 part is dynamic and the rest of the Index formula won't change if you copy it across the columns. Right?

       

      If you pay attention to the part...

      INDEX($F:$F,30

      This is equivalent to F30 as Index is looking at column F where row index is 30.

       

      But in the formula (COLUMNS($A1:A1)-1) is added to the row number and in the first formula cell, COLUMNS($A1:A1) will be evaluated as 1 and COLUMNS($A1:A1)-1 will be evaluated as 0 so nothing will added to the row index 30 in the formula in the first cell and Index formula will be converted to INDEX($F:$F,30) only and will refer to the cell F30.

       

      But when you drag the formula across the next column, (COLUMNS($A1:A1)-1) would change to (COLUMNS($A1:B1)-1) which is equivalent to 2-1 i.e. 1 and this 1 will be added to the row index 30 to make it 31. So the Index formula will become Index($F:$F,31).

       

      Similarly when this formula is copied to the next column it would become Index($F:$F,32) and so on and so forth.

       

      Hope this helps you to understand the formula better.

       

      If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.

      • JTaverna's avatar
        JTaverna
        Copper Contributor
        Dec 05, 2019

        Subodh_Tiwari_sktneer 

        Thank you, this makes sense and the formula is changing exactly as you describe, and working perfectly.

        My last question is why (COLUMNS($A1:A1)-1)?

        Is (COLUMNS($A1:A1), which is = 1, basically an incremental counter? Is it searching for how many columns are between the fixed $A1 and A1 = 1? I can see how it increases by 1 each time it is copied to give the desired result. Could I replace Columns with Rows to get the same result?

         

        Thank you, just trying to understand better 

Resources