Hello!
I'm trying to create a chart where the data range is based on what products the user selects, and the date range selected. There are two variables for every product. The user also has the choice to choose one or both variables for each product.
The Merged Data sheet has Dates in Column A, and each product has 3 columns, for example Product A data is in Columns B-D. Column B has the Work Order number, Column C has a count of work orders, and Column D has the associated customer service calls for that product on that day. It repeats every three columns for each new product. Along Row 1 the product name is in each cell (to help locate the appropriate data).
I had contemplated a pivot table, but based on the way I had to organize my Data I didn't think it would be feasible.
I have been working on the VBA but am having trouble connecting the Form controls to the data. I am assuming that I would write a macro where the product that is selected will be searched for on the merged data sheet. The work order data would then be offset by 1, and the customer service calls would be offset by 2.
I am assuming that using dynamic ranges will be necessary. The data will be periodically updated so I thought of having dynamic ranges for each column of data.
I created what I needed on a smaller scale using checkboxes and excel functions with the following formulas I have generalized, but when dealing with 200+ products I figured that I would need to use VBA to make it more efficient.
Dynamic Chart with Checkboxes:
o X Values
=OFFSET(Chart!$A$2,MATCH(StartDate,ChartDates,0)-1,0,MATCH(EndDate,ChartDates,0)-MATCH(StartDate,ChartDates,0)+1,1)
**$A$2 is where the data begins
o StartDate
Cell where the dropdown menu will be of the dates
=’Name of worksheet’!cell reference
o EndDate
Cell where the dropdown menu will be of the dates
=’Name of worksheet’!cellreference
o ChartDates
=Tabe1[Date]
Or, =’Name of worksheet’!rangeofdates
o ChartAmounts
=Table1[Amt]
Rest of data
o Putting it all together
Define a name for each y value range
- Yvaluerangename
o Define name with formula: =OFFSET(‘sheetname’!Xvalues,0,#ofcolumns from data)
Then, for that series plotted on the graph, use this formaul
=SERIES(‘sheetname’!referenceoftitle,’sheetname’!Xvalues,’sheetname’!Yvaluenameofyvaluerange,#of series plotted)
I am basically at a loss of where to begin. The macro for filling the data on the spreadsheet wasn't that difficult, but for some reason when it comes to charting I am at a loss! Any direction or suggestions would be MUCH appreciated!
Here's a link to a modified version of the workbook I'm working from. The macros have already been run that create the "Merged Data" sheet. RFC should be changed to Work Order but I didn't have time to alter all the VBA.
http://dl.dropbox.com/u/67322153/Interactive%20Chart%20Dynamic%20Ranges%20Workbook.xlsm
Thanks!
I'm trying to create a chart where the data range is based on what products the user selects, and the date range selected. There are two variables for every product. The user also has the choice to choose one or both variables for each product.
The Merged Data sheet has Dates in Column A, and each product has 3 columns, for example Product A data is in Columns B-D. Column B has the Work Order number, Column C has a count of work orders, and Column D has the associated customer service calls for that product on that day. It repeats every three columns for each new product. Along Row 1 the product name is in each cell (to help locate the appropriate data).
I had contemplated a pivot table, but based on the way I had to organize my Data I didn't think it would be feasible.
I have been working on the VBA but am having trouble connecting the Form controls to the data. I am assuming that I would write a macro where the product that is selected will be searched for on the merged data sheet. The work order data would then be offset by 1, and the customer service calls would be offset by 2.
I am assuming that using dynamic ranges will be necessary. The data will be periodically updated so I thought of having dynamic ranges for each column of data.
I created what I needed on a smaller scale using checkboxes and excel functions with the following formulas I have generalized, but when dealing with 200+ products I figured that I would need to use VBA to make it more efficient.
Dynamic Chart with Checkboxes:
o X Values
=OFFSET(Chart!$A$2,MATCH(StartDate,ChartDates,0)-1,0,MATCH(EndDate,ChartDates,0)-MATCH(StartDate,ChartDates,0)+1,1)
**$A$2 is where the data begins
o StartDate
Cell where the dropdown menu will be of the dates
=’Name of worksheet’!cell reference
o EndDate
Cell where the dropdown menu will be of the dates
=’Name of worksheet’!cellreference
o ChartDates
=Tabe1[Date]
Or, =’Name of worksheet’!rangeofdates
o ChartAmounts
=Table1[Amt]
Rest of data
o Putting it all together
Define a name for each y value range
- Yvaluerangename
o Define name with formula: =OFFSET(‘sheetname’!Xvalues,0,#ofcolumns from data)
Then, for that series plotted on the graph, use this formaul
=SERIES(‘sheetname’!referenceoftitle,’sheetname’!Xvalues,’sheetname’!Yvaluenameofyvaluerange,#of series plotted)
I am basically at a loss of where to begin. The macro for filling the data on the spreadsheet wasn't that difficult, but for some reason when it comes to charting I am at a loss! Any direction or suggestions would be MUCH appreciated!
Here's a link to a modified version of the workbook I'm working from. The macros have already been run that create the "Merged Data" sheet. RFC should be changed to Work Order but I didn't have time to alter all the VBA.
http://dl.dropbox.com/u/67322153/Interactive%20Chart%20Dynamic%20Ranges%20Workbook.xlsm
Thanks!
