Create a chart for a specific date range

Jackman1

New Member
Joined
Jan 13, 2015
Messages
26
Hi guys,

With my knowledge improving, especially with the help of MrExcel forums I've created in my humble opinion :) an excellent "live" spreadsheet that collects both forecast and sales data from the data selected from a dropdown product list in B1 using a SUMIFS....... fornula

To assist with reviews, I've also created the table in row 23&24 that extracts the data based the value I wish to review in more detail from another dropdown list, located in B22 (basically from the image example this grabs the matching forecast and sales of "20" - B4 and B13).

As data is to continue throughout the months and years, I would like the table to represent dates and results from a selected range, selected from the current empty cells in B25 (From date) and B26 (To date), without having to amend manually.

I've tried this via creating a INDEX and MATCH formula, which I believe then should be entered the same in Name Manager to allow the formula to work when entered into the series values within the table........
I'm struggling with creating a formula that works :( , maybe because my date is in columns rather than rows?

After hours of trying I've succumbed to asking for help.

As always, your support guidance is so appreciated.

Thank you
 

Attachments

  • Example.png
    Example.png
    56.1 KB · Views: 5

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Just solved it guys

Put the below into name manager;
date
=INDEX('Product Review'!$C$1:$ZZ$1,MATCH('Product Review'!$B$25,'Product Review'!$C$1:$ZZ$1,0)):INDEX('Product Review'!$C$1:$ZZ$1,MATCH('Product Review'!$B$26,'Product Review'!$C$1:$ZZ$1,0))
Forecast
=INDEX('Product Review'!$C$23:$ZZ$23,MATCH('Product Review'!$B$25,'Product Review'!$C$1:$ZZ$1,0)):INDEX('Product Review'!$C$23:$ZZ$23,MATCH('Product Review'!$B$26,'Product Review'!$C$1:$ZZ$1,0))
Sales
=INDEX('Product Review'!$C$24:$ZZ$24,MATCH('Product Review'!$B$25,'Product Review'!$C$1:$ZZ$1,0)):INDEX('Product Review'!$C$24:$ZZ$24,MATCH('Product Review'!$B$26,'Product Review'!$C$1:$ZZ$1,0))

Very happy!

Any streamlining comments still appreciated!
 
Upvote 0
Solution

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top