PDA

View Full Version : interactive chart



arnab0711
02-16-2012, 06:22 AM
Hi,
I have this excel workbook which has 5 sheets KOL,WB,Assam,Final,Graph
the final sheet should contain the sum of all sheets
The graph sheet has a graph based on final sheet.
Now I want to create a combox form control and at change of each item in combo box the data will
change accordingly.For example if I select PDH Antenna then it will show all the data partaning to
PDH antenna of all 12 sheets.

Bob Phillips
02-16-2012, 07:03 AM
Where do those numbers on Final come from?

arnab0711
02-16-2012, 07:07 AM
Actually the numbers should be the sum of all sheets but here I have taken arbitrary numbers just to show the charts.

Bob Phillips
02-16-2012, 07:57 AM
Try this

arnab0711
02-16-2012, 09:38 AM
Hi,Thanks for the solution,but if I don't want the sum only a particular number what will be the formula in final sheet

Bob Phillips
02-16-2012, 09:46 AM
What particular number do you mean?

arnab0711
02-16-2012, 10:01 AM
for example if I select PDH antenna in the combo box we will see the D4 figure of
Assam in Final sheet as "44" which is the sum of 4 months,but my requirement is for one month,because I want to add another spinner button to the chart that will traverse the months.

Bob Phillips
02-16-2012, 10:28 AM
First thing you need to do is remove those merged date cells and put the date in each row, then change the formula to SUMIFS with the extra condition.

arnab0711
02-16-2012, 10:57 PM
Hi,
I have a little problem,if I remove the sumproduct its showing #value,can you help me with the problem.

Bob Phillips
02-17-2012, 02:54 AM
Why are you removing it?

arnab0711
02-17-2012, 02:59 AM
to get figure for one month only.not the sum of all months.

Bob Phillips
02-17-2012, 03:33 AM
Read what I said in post #8.

arnab0711
02-17-2012, 04:23 AM
I have removed the merged cells,but I am little confused about the extra conditions in sumifs

Bob Phillips
02-17-2012, 04:33 AM
Post the revised workbook, i.e. no merged cells, and I willdo it for you.

arnab0711
02-17-2012, 04:42 AM
Hi,
Pl find the spreadsheet with unmarged cells

Bob Phillips
02-17-2012, 05:07 AM
The formulae are correct here, but your dates are bad. For some reason you entered the 1st for Jan, but the 2nd for Feb, Mar,.... You need to change them all to the 1st.

arnab0711
02-17-2012, 05:15 AM
Thanks its working,can you also help me with the check boxes in the graph sheet,my objective is if I select any particular check box on that details(stack)will be shown,or else it won't be shown

Bob Phillips
02-17-2012, 05:52 AM
Maybe this is okay

arnab0711
02-17-2012, 06:11 AM
Hi,
In certain places the figures are not coming,for example PDH Antenna for WB sheet,although there are figures they are not showcasing in Final sheet an subsequently now showcasing in graph as well.

Bob Phillips
02-17-2012, 06:22 AM
That is because WB is not in the list of checkboxes, I couldn't correlate that list to the worksheets.

Bob Phillips
02-17-2012, 06:25 AM
You could try amending the formulae to

=IF(ISNUMBER($B4),IF(INDEX(Graph!$E$7:$E$21,Final!$B4),
SUMPRODUCT(SUMIFS(INDIRECT($C4&"!"&D$1),INDIRECT($C4&"!"&$C$1),INDEX($A$1:$A$4,$D$2),INDIRECT($C4&"!"&$C$2),DATE(Graph!$A$1,Final!$E$2,1))),NA()),
SUMPRODUCT(SUMIFS(INDIRECT($C4&"!"&D$1),INDIRECT($C4&"!"&$C$1),INDEX($A$1:$A$4,$D$2),INDIRECT($C4&"!"&$C$2),DATE(Graph!$A$1,Final!$E$2,1))))

but I think you should sort out that list.

arnab0711
02-18-2012, 03:27 AM
thanks,I have changed the names and its working now but i have request for you,the formula seems little complicated,if you remember the first one
=SUMPRODUCT(SUMIF(INDIRECT($C5&"!"&$C$1),INDEX($A$1:$A$4,$D$2),INDIRECT($C5&"!"&E$1)))
this was easy to understand,but when you have given this formula
=IF(ISNUMBER($B4),IF(INDEX(Graph!$E$7:$E$21,Final!$B4),SUMPRODUCT(SUMIFS(IN DIRECT($C4&"!"&D$1),INDIRECT($C4&"!"&$C$1),INDEX($A$1:$A$4,$D$2),INDIRECT($C4&"!"&$C$2),DATE(Graph!$A$1,Final!$E$2,1))),NA()),NA())
its complicated,so if you can guide me as to how to read this formula,it will very helpful.

Bob Phillips
02-18-2012, 08:01 AM
The SUMIF is modified to a SUMIFS so as to accommodate multiple condition tests.

matches:= SUMPRODUCT(SUMIFS(IN DIRECT($C4&"!"&D$1),INDIRECT($C4&"!"&$C$1),INDEX($A$1:$A$4,$D$2),INDIRECT($ C4&"!"&$C$2),DATE(Graph!$A$1,Final!$E$2,1)))

which checks the data that matches the code and the date and sums it

check_test:= IF(INDEX(Graph!$E$7:$E$21,Final!$B4),matched,NA())

checks if the worksheet code on this row has the cehckbox set, if so it does the sum, else it outputs NA() (so that it doesn't get graphed)

=IF(ISNUMBER($B4),check_test,NA())

checks if there is a number in B4, which signifies that the codes on Graph have a corresponding worksheet name, if so does the index test, else it outputs NA() (so that it doesn't get graphed)

If you want complicated requirements, you get complicated formulae.

arnab0711
02-19-2012, 01:22 AM
thanks for helping me understanding the topic

arnab0711
02-19-2012, 02:14 AM
Hi,
I want to add another chart to existing graph sheet based on table from final sheet,which has two combo boxes,one for month and another for material type.

Bob Phillips
02-19-2012, 04:52 AM
Apply the same principles as before.

arnab0711
02-19-2012, 05:51 AM
Hi,
I have used the same procedure but its giving an error value,can you have a look at it.

Bob Phillips
02-19-2012, 07:09 AM
The input range for the material type dropdown should be Assam!$V$2:$V$7.

The range in Final!T3 should be L4:L400.

The value formula should be
=SUMPRODUCT(SUMIFS(INDIRECT($L4&"!"&$M$2),INDIRECT($L4&"!"&$L$2),INDEX($P$3:$P$8,$N$1),INDIRECT($L4&"!"&$L$3),DATE(Graph!$A$1,$M$1,1)))

The Qty formula should be
=SUMPRODUCT(SUMIFS(INDIRECT($L4&"!"&$N$2),INDIRECT($L4&"!"&$L$2),INDEX($P$3:$P$8,$N$1),INDIRECT($L4&"!"&$L$3),DATE(Graph!$A$1,$M$1,1)))

arnab0711
02-19-2012, 08:05 AM
Hi,
Still showing this error

Bob Phillips
02-19-2012, 08:52 AM
The input range for the material type dropdown should be Assam!$V$2:$V$7.

You changed the date combobox not the material combo.


The range in Final!T3 should be L4:L400.

I should have said Final!L3.

arnab0711
02-19-2012, 10:31 PM
Ok,I am a little confused now,I want to keep the material combobox and the logic combobox and now its showing #ref!

arnab0711
02-20-2012, 07:14 AM
Hi Xld,
Please help me,in solving this.

Bob Phillips
02-20-2012, 09:14 AM
This is it

arnab0711
02-20-2012, 09:46 AM
Thank you so much,I really don't have any any words to show my gratitude towards you.The amount patience you have shown is really appreciable.

Bob Phillips
02-20-2012, 02:55 PM
I just hope it does you in good stead in your job :)