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

#### sampahmel

##### New member
[h=1]
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.​
View attachment Fruits.xlsx
[/h]

#### NBVC

##### Super Moderator
Staff member
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.

#### Attachments

• Fruits.xlsx
19.9 KB · Views: 7

#### sampahmel

##### New member
Thanks NBVC for the help.

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.

Staff member