Chart with drop down selection

Hi there,

In order to be able to select data by location, you need to have that field in your source data on Sheet 2.

A simple way to allow you to filter your data/chart is to use a 'slicer'.

I have expanded your data table on Sheet 2 a bit more so we actually have more than one location and added 2 slicers on Sheet 3.

You can select one or more Location(s) (use "Ctrl" to add/remove additional locations).
With the combination of the 2 slicers you have various options to show specific items, specific location(s) or specific item(s) by specific location(s).

There are other options with formulas if you don't want to use slicers, but a bit more involved with the setup.

Trust this helps.

Hi again,

Yes it is possible to have more 'slicers'. The pre-requisite is that you have a column with them in your data source.
I can see that you have items in column A on Sheet 1, but they are not in the table in Sheet 2 which is the source for the chart.
Similarely as I did insert the Location column on Sheet 2, you need to include a column with the Item numbers.

Hi,
Depanding on the complexity this maybe can be done with lookup formulas, but maybe VBA is a better option.

Eitherway, we need to understand your data sample a bit better.
Your data on Sheet 1 should match the data on Sheet 2 so we have a 'Current' and 'Required' set.

On Sheet 1, what do the 'x' represent. If this should be numbers, it would be helpful for some actual data which then corolates to Sheet 2 to validate possible solutions.
On Sheet 1, you have multiple 'Items' for 'Location 1'. Do you require each 'Item' and 'Location' separately with the 4 rows on Sheet 2, or can they be summarised and list the total just by 'Location'.

For Plan, I can see that Q3+R3 from Sheet 1 translate to the 16 for Jan on Sheet 2 in D4, then the cell values from Feb to Dec.
For Demand, C3+D3 from Sheet 1 translates to the 19 for Jan on Sheet 2 in D5, then the cell values for Feb to Dec.

On Sheet 2, in cells C6 & C7, where is the 79 from? For 'Location 1' row 5 'Demand' for Jan - Dec = 79. This logic however does not work for 'Location 2' or 'Location 3'.

Hi again,

Please refer to the attached file.

I have added some formulas which will pickup the values in each row based on the Location and Item form Sheet 1.
The formulas are dynamic, so you can copy/past the blocks of 4 rows for as many as you need. You will need to extend the range in the required rows (currently only 14).
Also make sure the table is resized to ensure any added blocks are included in the table for the formulas to pick up the ocrrect values.

You will aslo need ot manually set the Location and Item refrences (yellow). Once setup, it should pick up the values when Sheet 1 changes.
Maybe someone else with VBA skills could automate this process.

You are welcome.

If your Question is answered; please mark it SOLVED. You can do this via the Thread Tools at the top of your original post.

Also, if you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

Hi,

Sorry for reopening this post. I have one issue and i dont know where i am going wrong.

I have 100s of rows and for each separate location and item do i need to do this individually? or is there any formula where i can do it automatically?




Thanks

Hi krishnakumar121988,

I have one issue and i dont know where i am going wrong.

Can you please be more specific. What is going wrong, what is the issue?

Screenshots are not really that helpfull as we can't see what is actually going on in the file, settings, formulas etc.
Your screenshot is for the allocation of primary and secondary axis on the chart. Do you need specific items show as line and others as bar chart?
New items/locations will be allocated the default line chart. If you want specific items to show as bars, you need to identify them as such.

Also please check:
If you have worked of the file above and copied the 4 line boxed for as many set as you need, check that the formulas work and the correct data is in the table.
Reset/resize the table on Sheet 2: click anywhere in the table. On the top ribbon go to 'Table Tools', then at the far left under 'Resize Table' make sure the table is extended all the way down to the bottom of the data.

On Sheet 3 click in the chart. On the top ribbon go to Chart tools, then Design. Check the 'Select Data' icon. Check the Chart Data Range is covering all of Table2.

It should automatically pick up new data and update the chart and slicers.