OFFSET with named range

  • I'm working with a cost model developed by someone else. On one tab, there is a named data range for quantities bought in each year. The named data range starts in Column D. On another tab, beginning in Column F, there is a formula that pulls from the named data range. The obvious problem is that it starts in Column F, so pulls the third year's data rather than the intended first year's data. I'd rather not shift all the formulas to Column D. Is there a way to use the OFFSET formula with a named range?


    INDEX works, but requires me to put an index number in a row.


    Attached is an Excel file. The named data range is on Sheet1, the formula is on Sheet2.


    Thanks for any help or suggestions!


    Daveforum.ozgrid.com/index.php?attachment/42183/

  • Re: OFFSET with named range


    You can use the column function to remove the index specification.


    =IF(INDEX(NamedRange,0,COLUMN()-9)=0,0,INDEX(NamedRange,0,COLUMN()-9))

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!