Results 1 to 5 of 5

Thread: Prices of fruit via types (i.e. apple/orange) and dates (i.e. year/quarter)

  1. #1

    Smile Prices of fruit via types (i.e. apple/orange) and dates (i.e. year/quarter)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I am looking to draw data (i.e. price of fruits) from one tab to another tab based on dates (by year or by quarter) and criteria (i.e. types of fruit).

    The complication arises because the data will expand as the dates progress. For example, the prices will be updated until 2014. Therefore, I suppose there's a need to use dynamic ranging (via OFFSET(COUNTA())?).

    Can you look into this in the attached spreadsheet.

    Thank you.
    Fruits.xlsx



  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    See attached.

    First, I would change column B in the Year tab, to just show the YEAR.

    Then make a dynamic range called YearData with formula: =OFFSET(Year!$B$4,0,0,COUNTA(Year!$B:$B)+1,7)

    and make a dynamic range called QuarterData with formula: =OFFSET(Quarter!$B$4,0,0,COUNTA(Quarter!$B:$B)+1,7)

    Now formula in Sheet 1, B6 is: =INDEX(YearData,MATCH(B$5,INDEX(YearData,0,1),0),MATCH($A6,INDEX(YearData,1,0),0)) copied down and to column D

    Formula in Sheet 1, E6 is: =INDEX(QuarterData,MATCH(E$5,INDEX(QuarterData,0,1),0),MATCH($A6,INDEX(QuarterData,1,0),0)) copied down and across to end.
    Attached Files Attached Files


  3. #3
    Thanks NBVC for the help.

    Quote Originally Posted by NBVC View Post
    See attached.

    First, I would change column B in the Year tab, to just show the YEAR.

    Then make a dynamic range called YearData with formula: =OFFSET(Year!$B$4,0,0,COUNTA(Year!$B:$B)+1,7)

    and make a dynamic range called QuarterData with formula: =OFFSET(Quarter!$B$4,0,0,COUNTA(Quarter!$B:$B)+1,7)

    Now formula in Sheet 1, B6 is: =INDEX(YearData,MATCH(B$5,INDEX(YearData,0,1),0),MATCH($A6,INDEX(YearData,1,0),0)) copied down and to column D

    Formula in Sheet 1, E6 is: =INDEX(QuarterData,MATCH(E$5,INDEX(QuarterData,0,1),0),MATCH($A6,INDEX(QuarterData,1,0),0)) copied down and across to end.

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,469
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Multiple cross poster http://www.excelforum.com/excel-gene...ml#post3746825 ( and others)

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    sampahmel,

    Please read this article to find out why you have been called out for cross posting: http://www.excelguru.ca/content.php?184


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •