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

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

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. 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.

3. Thanks NBVC for the help.

Originally Posted by NBVC
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. Multiple cross poster http://www.excelforum.com/excel-gene...ml#post3746825 ( and others)

5. sampahmel,