Announcement

Collapse
No announcement yet.

Drag Every Nth Column, with varying starting column by Date

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Drag Every Nth Column, with varying starting column by Date



    I have attached a sample worksheet, the formula below assists in determining the starting cell.

    =INDIRECT(TEXT(SUBSTITUTE(ADDRESS(1,SUMPRODUCT(--(YEAR($E6)=YEAR($G$5:$O$5)),$G$4:$O$4)+COUNTBLANK($A$5:$F$5),4),1,""),0)&ROW($E6))

    The goal is to use a formula similar to: =OFFSET($C$5,0,(COLUMN(A8)*3)-1) and drag a result (in this case "$2,500) from the starting cell, and every five years thereafter. Any assistance would be appreciated.
    Attached Files

  • #2
    Solved myself, by changing column letter using dates.

    =IF($E$6>I$5,0,CHAR(CODE(IF(MOD(YEARFRAC($E6,I$5),$H$12)=0,$G$11,0))+IF($E$6>I$5,0,IF(MOD(YEARFRAC($E6,I$5),$H$12)=0,YEARFRAC($E6,I$5)))))

    Comment


    • #3
      thanks for sharing
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

      Comment


      • #4


        Originally posted by pike View Post
        thanks for sharing
        Thanks, my solution does have one unintended flaw - perhaps you can help still. When the number rises above 90, it doesn't switch to column AA. Any ideas how to make the solution perpetual?

        Comment

        Working...
        X