Results 1 to 2 of 2
Thread: Automating charts (Excel 2003)
2004-07-29, 19:15 #1
- Join Date
- Jul 2004
- Thanked 0 Times in 0 Posts
Automating charts (Excel 2003)
I have a large report that I've been given to master and maintain. I've learned quite a bit about Excel recently by reading a "bible" manual, but don't know how to automate one of the tasks I'll have to maintain monthly.
My main worksheet with all the information, has, among other column titles, ones that are "Volume", "SOP" (date), and several columns with different features in each one. All of the Features columns will have an "X" typed in if that program contains that specific feature. I then have to track specific Features on charts.
Right now, I Filter all jobs that have Feature A (designated with an 'X' in its cell). Then I go to my Chart worksheet and basically copy/paste all the data I need from each line item - Customer program, Volume, and SOP date. I then have a last column for my chart that creates a cumulative, running volume also. I sort the resulting data by the SOP date with the earliest first. My end chart shows a Line Chart tracking the cumulative volume, and then Bar Charts tracking each individual Customer program, showing its individual volume below the cumulative line. Looks great and works great, but some of the "Features" will contain 40-50 different customer programs, which I have to copy/paste back and forth inbetween worksheets.
Is there a way that I can do this automatically? Create a formula or macro or something that basically says "If 'Feature A' on Worksheet 1 is equal to 'X', then copy the Volume from Worksheet1 to Worksheet2, and copy the SOP date from Worksheet1 to Worksheet2." ?
2004-07-29, 20:47 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Automating charts (Excel 2003)
You might use Data | Filter | Advanced Filter... to copy the rows with X in a specific feature column. Set up criteria ranges for each of the features, and extract ranges.
Note: if you want to use Advanced Filter to copy rows to another worksheet, you must select Data | Filter | Advanced Filter... while the destination sheet is active. If the source sheet is active, Excel won't allow you to specify a target range in another worksheet.
(Small demo attached.)