# Formula to retrieve all the data between selected months

#### Shweta

##### Well-known Member
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>

Thanks & regards,
Shweta

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Assuming the main table resides in A1:C13, January resides in E2 and April resides in F2, try:

Code:
``=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

Thanks Matty!

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

Hi Matty,

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

Thanks Matty!

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:

Code:
``=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

Replies
6
Views
630
Replies
4
Views
548
Replies
3
Views
460
Replies
2
Views
423
Replies
5
Views
648

1,203,061
Messages
6,053,307
Members
444,651
Latest member
markkuznetsov1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back