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

sampahmel

New member
Joined
May 14, 2014
Messages
3
Reaction score
0
Points
0
[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
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

Attachments

  • Fruits.xlsx
    19.9 KB · Views: 7

sampahmel

New member
Joined
May 14, 2014
Messages
3
Reaction score
0
Points
0
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.
 
Top