Formula to retrieve all the data between selected months

[Shweta]

Hi All,

I have the below table

month	amount	data
january	1234	12345
february	1345	13425
march	1456	14524
april	1567	15625
may	1657	16524
june	1765	17652
july	1867	18655
august	1987	15676
september	2123	18765
october	2212	19876
november	2343	21233
december	1432	22765

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

I put data validation in two different cells for month . Now I want when I select the months in these cells all the data (Amount,Data) between selected months should appear in my sheet.

For ex:

In one cell I have selected "January" and in second cell "April".

Now Data between these two months should appear like below:

amount	data
1234	12345
1345	13425
1456	14524
1567	15625

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

Please help me out on this.

Thanks & regards,
Shweta

 

[Matty]

Assuming the main table resides in A1:C13, January resides in E2 and April resides in F2, try:

=IFERROR(INDEX(INDEX(B$2:B$13,MATCH($E$2,$A$2:$A$13,0)):INDEX(B$2:B$13,MATCH($F$2,$A$2:$A$13,0)),ROWS(H$2:H2)),"")

Where the second table start in H2 (excluding headers). Copy down to H13 and across to I13.

Matty

 

[Shweta]

Thanks Matty!

Your formula is working perfectly.

One more question is there a way to keep the chart range dynamic?

For Ex: E2:Jan and F2: Dec

Output is in the range H2:H13. I prepared a chart using this data(from H2:I13)

now E2:Jan and F2:May

Output is in the range H2:H6, but chart is still from H2:I13,

Is there a solution for it so that chart can change according to the range of output data

 

[Shweta]

Hi Matty,

Can u please help me with a VBA code for the same task?

 

[Shweta]

Can you please convert this excel formula into VBA Code that would be better.

 

[Matty]

Thanks Matty!

Your formula is working perfectly.

One more question is there a way to keep the chart range dynamic?

For Ex: E2:Jan and F2: Dec

Output is in the range H2:H13. I prepared a chart using this data(from H2:I13)

now E2:Jan and F2:May

Output is in the range H2:H6, but chart is still from H2:I13,

Is there a solution for it so that chart can change according to the range of output data

I'm glad it's working for you.

This part of the formula:

=INDEX(B$2:B$13,MATCH($E$2,$A$2:$A$13,0)):INDEX(B$2:B$13,MATCH($F$2,$A$2:$A$13,0))

Could be used as the formula in a dynamic named range. You could then point your chart to this named range.

Regarding your request for VBA, I'm not much of a coder so perhaps someone else can help you with this.

Matty