Chart data from multiple cells on separate sheet through combobox

Hi,

Attached is my workbook example, 2 charts on sheet 2 collect data from sheet 1. The "week" combo boxes on sheet 1 and 2 display the data correctly in chart 1.

I have sheet 3 setup to display the data in sheet 1 and 2 "period" combo boxes which are the periods defined in sheet 1 B6:B57

What I am looking do is select a period in "period" combo boxes on sheet 1 & 2 and have this return data to sheet 2 row 23 & 24

Row 23 display the week numbers for that period (sheet 1 column C), this will change between 4 and 5 weeks at times
Row 24 display the average percent for that week (sheet 1 column M)

I can then use this information to populate chart 2

I would like chart 2 to work in the same way chart 1 does but for the period not the week

Thanks
Tez

Could you manually demonstrate your expected outcome so we can see what you're after?

i.e. do you want to sum the values of a selected period? Average them?

In chart 1 you have days of the week. What would be the equivalent in chart 2?

BSB

Thanks for you reply,

I've updated the example workbook to show what I'm looking to achieve.

Sheet 2 Row 23 to show weeks relating to period selected in the combo box, these weeks are from Sheet 1 C6:C57.
Period 3 being weeks 10-13, if period 2 were selected then this would be weeks 5-9 etc.

Sheet 2 Row 24 to display the week average taken from Sheet 1 Column M, so for period 3 would be Sheet 1 M15:M18 and period 2 M10:M14 etc.

This data is then used to populate chart 2

In doing this, is it possible to have the chart show the relevant week numbers on the horizontal legend rather than 1-7 and also for Rows 23:24 to only show related weeks.
I.e. currently chart 2 shows 1-7 as that is the data I've selected, but where there are 4 weeks in the period I would want just the 4 weeks showing in the chart, likewise if there were 5 weeks in the period.

I hope this makes sense.

Again thanks
Tez

Put this in A23 on Sheet2. It's an array formula so needs to be confirmed with Ctrl+Shift+Enter.

Formula:

Please Login or Register  to view this content.

Put his in A24 on Sheet2.

Formula:

Please Login or Register  to view this content.

Copy them both right as required.

To make the chart axis show the week numbers you just adjust that in the normal way to make it look at row 23.

I've used two dynamic named ranges to take care of chart 2 only showing the relevant number of weeks.
Press Ctrl + F3 to open the named range manager to see how that's done.

See attached and feel free to ask any questions.

BSB

Works exactly as I intended, thanks.

I guess its easy when you know how but I can only manage to get around the basics at the moment. Its good to try and work out how the formulas work though.

Again thanks.

Happy to help

Yes, easy when you know how, but we all started somewhere. My somewhere was the best part of 20 years ago.

Shout for help if you need any of it explained, but certainly worth a little digging around to see how much you can work out for yourself first.

BSB