Vertical Data Extraction

Hi,

I need to create a chart that is based on changing values. The Values are 
all stored vertically in a Worksheet called Percentage. The chart sheet 
contains a selection box where the user can select a specific search and a 
date and the selected conditions should be searched for and the chart should 
display results.

Basically, when the user selects X in the first list box and Y in the second 
one, the code should search the Percentage worksheet for all instances of 
value=x and y and output.

column Row Feature1 Feature2 Feature3
2000            0.5         8.3         10
2000            1.5         4.6         1
2001            5.5         6            4
2001            0.7         3            20

So if the user selects Feature1 in the year 2001, the values in Feature1 and 
2001 should be output.

Any help would be appreciated!

Thanks

Funda 
0
Funda (2)
1/6/2006 2:22:01 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
961 Views

Similar Articles

[PageSpeed] 40

Funda -

You could set up the data differently, and use an AutoFilter. One column 
would have Year, another for Feature, and the third for the value. Select 
AutoFilter from the Data menu.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Funda" <Funda@discussions.microsoft.com> wrote in message 
news:0ABFF0ED-070E-46D4-A1F0-91CDBDC20D4C@microsoft.com...
> Hi,
>
> I need to create a chart that is based on changing values. The Values are
> all stored vertically in a Worksheet called Percentage. The chart sheet
> contains a selection box where the user can select a specific search and a
> date and the selected conditions should be searched for and the chart 
> should
> display results.
>
> Basically, when the user selects X in the first list box and Y in the 
> second
> one, the code should search the Percentage worksheet for all instances of
> value=x and y and output.
>
> column Row Feature1 Feature2 Feature3
> 2000            0.5         8.3         10
> 2000            1.5         4.6         1
> 2001            5.5         6            4
> 2001            0.7         3            20
>
> So if the user selects Feature1 in the year 2001, the values in Feature1 
> and
> 2001 should be output.
>
> Any help would be appreciated!
>
> Thanks
>
> Funda 


0
jonxlmvpNO (4558)
1/7/2006 4:19:44 AM
Thanks John,

I have done that already. I just need to know how you get the chart working. 
How do you get the chart to filter automatically based on user choice?

"Jon Peltier" wrote:

> Funda -
> 
> You could set up the data differently, and use an AutoFilter. One column 
> would have Year, another for Feature, and the third for the value. Select 
> AutoFilter from the Data menu.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
> 
> "Funda" <Funda@discussions.microsoft.com> wrote in message 
> news:0ABFF0ED-070E-46D4-A1F0-91CDBDC20D4C@microsoft.com...
> > Hi,
> >
> > I need to create a chart that is based on changing values. The Values are
> > all stored vertically in a Worksheet called Percentage. The chart sheet
> > contains a selection box where the user can select a specific search and a
> > date and the selected conditions should be searched for and the chart 
> > should
> > display results.
> >
> > Basically, when the user selects X in the first list box and Y in the 
> > second
> > one, the code should search the Percentage worksheet for all instances of
> > value=x and y and output.
> >
> > column Row Feature1 Feature2 Feature3
> > 2000            0.5         8.3         10
> > 2000            1.5         4.6         1
> > 2001            5.5         6            4
> > 2001            0.7         3            20
> >
> > So if the user selects Feature1 in the year 2001, the values in Feature1 
> > and
> > 2001 should be output.
> >
> > Any help would be appreciated!
> >
> > Thanks
> >
> > Funda 
> 
> 
> 
0
Funda (2)
1/8/2006 9:41:03 PM
Make a chart based on all the data, without the filter applied. Then select 
the chart, go to Options on the Tools menu, Chart tab, and make sure to 
check Plot Visible Cells Only.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Funda" <Funda@discussions.microsoft.com> wrote in message 
news:B7C8F1F2-3E24-4D22-8E62-D2817DF1F501@microsoft.com...
> Thanks John,
>
> I have done that already. I just need to know how you get the chart 
> working.
> How do you get the chart to filter automatically based on user choice?
>
> "Jon Peltier" wrote:
>
>> Funda -
>>
>> You could set up the data differently, and use an AutoFilter. One column
>> would have Year, another for Feature, and the third for the value. Select
>> AutoFilter from the Data menu.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>> "Funda" <Funda@discussions.microsoft.com> wrote in message
>> news:0ABFF0ED-070E-46D4-A1F0-91CDBDC20D4C@microsoft.com...
>> > Hi,
>> >
>> > I need to create a chart that is based on changing values. The Values 
>> > are
>> > all stored vertically in a Worksheet called Percentage. The chart sheet
>> > contains a selection box where the user can select a specific search 
>> > and a
>> > date and the selected conditions should be searched for and the chart
>> > should
>> > display results.
>> >
>> > Basically, when the user selects X in the first list box and Y in the
>> > second
>> > one, the code should search the Percentage worksheet for all instances 
>> > of
>> > value=x and y and output.
>> >
>> > column Row Feature1 Feature2 Feature3
>> > 2000            0.5         8.3         10
>> > 2000            1.5         4.6         1
>> > 2001            5.5         6            4
>> > 2001            0.7         3            20
>> >
>> > So if the user selects Feature1 in the year 2001, the values in 
>> > Feature1
>> > and
>> > 2001 should be output.
>> >
>> > Any help would be appreciated!
>> >
>> > Thanks
>> >
>> > Funda
>>
>>
>> 


0
1/9/2006 12:58:31 AM
Reply:

Similar Artilces:

Data Analysis disappears from the Data ribbon for no reason
Every now and then (usually at the most inconvenient moment) Data Anylysis disappears from the data ribbon. If I return to Excel Options to try to reload it, it says it is already loaded. ...

admin: 4 Millions Domains data with Category
Successfull Internet and Direct Marketing products on www.promotionsite.net * NEW * DOMUS Domains Toolkit Fall 2004 - Unique on the Net 4 Millions "Whois" Domains data with Expiration Date and Category*. Ultimate Version (October 2004) - Our best rate starts from US *$149*. A wonderful tool for Internet and Direct Marketing. Available in Basic, Advanced and Full Editions. It contains a domain database with 4 millions *FRESH* October 2004 New records .com, .net, .org. 4 Gigabytes MS Excel data zipped on CD-Roms/Dvd. Compression 3:1. MS Excel or Text tab del...

Registry Location of Data Files
Hello, I help in managing a pretty large network. As we are sadly using pretty old computers people tend to crash\change computers pretty often. To prevent large data loss they save important mail either on the exchange server or in a personal folder the .pst file of which usually should sit on their private network folder. I have a small scripting applet running on each computer capturing a snapshot of the computer status and saving it to a database on the network. I would like it to also add a column for the location of the outlook data files, so that I can see which users save their .pst fi...

Exporting CRM data/cases
Hi...I'd like to export my data/cases from CRM and need help in identifying a table (SQL) that would contain all data, including notes and attachments. I know there's an AnnotationsBase table and assume that's where the notes/attachments are but cannot find a way to link them back to the actual case. Thoughts? For each entity you have a so called "filtered view" in the <company name>_MSCRM database. This views are "supported" way of getting data from CRM. KTF wrote: > Hi...I'd like to export my data/cases from CRM and need help in identifyi...

DEADLOCK_ENUM_MUTEX and SQL Server Data Collector
I recently implemented the Performance Datawarehouse on our production server after running it in our test environment for months with no problems. I am currently only collecting the 3 basic collection sets. Today 2 'SQL Server Data Collector' spids got hung and will not rollback. Normally the collection jobs take about 1.5minutes according to the log. Today though, the QueryStatisticscollection job was running for over 30minutes and causing the ActivityMonitor to error. I stopped the collection sets and the associated jobs. This did nothing to alleviate the problem. The ...

import data to sales
Hello! I'm relatively a newbie to GP (less than a year; used SQL much longer). I have to import data from our order software into GP (by way of Access): TRX > SALES TRX > SALES TRX ENTRY > ORDER (before anything is transferred to an invoice and posted). I have done database traces and have come up with an overwhelming # of tables where I'd enter data directly into GP SQL tables. I think I only need the SOP10103, SOP10100, SOP10200, SOP10104 tables but am afraid of missing tables (such as where the Distribution > Distribution Reference accounts info is). The trace als...

I want to display a graph vertically
Hello, I have a problem with displaying a graph in excel. It seems like the default way of displaying a graph is from left to right, that is, the longer part stretches out to the right. On my graph, dates are on the left (vertically) and an other factor is on the right (horisontally). I want the dates tp be horisontally and the other factor vertically, and the curve/graph should go from top to bottom, like a waterfall if that better explains it. That is, I would like to rotate the graph, but not by saving the graph as a gif-file and rotate it in Visio for instance, but as a real excel-graph, ...

Sort a data without affecting the scatter chart
Hi, I have a have pair of (x,y) values in a big 2 columnrange and I have made a scatter chart from,only part of this range.(say from last 20 values) Values in x column are sorted when i made the chart ,but when i sort the data from y values now,my scatter chart goes for a toss and sorting the data completely disfigures the chart. (as there are new (x,y ) values now in the range from which i made the range). How can i sort the my data without affecting the scatter chart? Thanks, Piyush Piyush - In Excel 2003 and earlier versions, do not use the AZ or ZA icons on the standard toolbar. I...

how to draw text vertically?
for example, some software like Microsoft Word, when docking a toolbar to left or right, the text on button will be drawn vertically. In article <eShIl7FZGHA.3704@TK2MSFTNGP03.phx.gbl>, Bill Gates says... > for example, some software like Microsoft Word, when docking a toolbar to > left or right, the text on button will be drawn vertically. Basically you specify the angle in lfEscapement of the LOGFONT structure. Then call CreateFontIndirect with this structure and you'll get a rotated font of type HFONT. Use SelectObject to select the font and then you can use TextO...

Data Stroage for Windows 2008 R2 Hyper-V servers
We would like to change our backup solution from Symantec to DPM 2010. I am unclear with how much storage is required for backups of our Hyper-V servers. We currently backup our servers to disk. If I have a Windows 2008 R2 Hyper-V host with 4 vms running on that host, and each vm has 10GB of storage (40 GB total), it seems that my first backup will take approximately 40 GB. When I do my second backup, will it require an additional 40 GB of storage, or will it only store the changed files on the four VMs? How will the third backup behave? Our hosts are configured with clustere...

Setting Column Width on Imported Data
Hi all, I would like to know if it is possible to set the column width of a column I am importing to Excel from Access. I need this column for what I'm doing in the Excel file, but I don't want it so wide, or would even like it hidden. Problem is everytime the data gets updated the column goes back to really wide. Any suggestions would be greatly appreciated. Thanks, Lori. ...

Data entry form #2
Hi everyone! I need to create a data entry form in excel. I need only two fields Stock no. & quantity. As soon as I enter the stock no. the curser should go to the quantit column and when I press enter it should store that data in my exce sheet ( in A1 & B1) and clear the message box for the next entry. Th next entry should be store in A2 & B2. Can someone send me the code for it. Appreciated Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thr...

Analysis Cubes Sample and Live Data
I want to create analysis cubes for the Sample company as well as for the live company. It is important to have these cubes for sample data so that the users can play and get used to working with this tool. Because of the dates (2010 - 2017) in the Sample Company Fabrikam, I cannot create the cubes in the same analysis cubes Data Warehouse. Should a different data warehouse be created for the sample company? -- Patti I would install two different instances of the analysis cubes. 1-Install the analysis cubes using the default names from the installer. I think the database and An...

Help comparing data in 2 columns and finding same data in both
Hi, I have a 2 column spreadsheet with numerical data in each column. I need to compare columns and note which numbers are in both columns (preferably in a third column) example: I have this... col A col B 1001855 1001855 1001855 1001866 1001866 1001877 1001866 1001888 1001877 1001899 I need this.... col A col B col C 1001855 1001855 X 1001855 1001866 X 1001866 1001877 X 1001866 1001888 1001877 1001899 A has over 14,000 lines and B has over 10,000 either may have duplicates. Suggestions? Thanks Max Max One way: =IF(COUNTIF(A:A,$B1)>0,"X&qu...

Consolidate Data
Want to consolidate data from many flies which are in the same format to a sigle file how can i do it in the best way? You can use a DIR looping macro to open or create formulas for each workbook desired to the next available row on the destination file. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Melwin" <Melwin@discussions.microsoft.com> wrote in message news:DA906967-E126-4101-A372-76B95AB79B6A@microsoft.com... > Want to consolidate data from many flies which are in the same format to a > sigle file how can i do it...

Can I display the actual data in the data field of a pivot chart?
Using Excel 2003, I am creating a pivot table using customer data similar to the following: Customer Location Interface Code 11 Word 123 12 Excel 13 Outlook 456 21 Word 22 Excel ABC 23 Outlook XYZ I am placing the Customer Location in the row area, the Interface in the column area and the Code in the data area. I would like the Code to show in the pivot table as the "123", "4...

I would like the tabs in Excel to be listed vertically on left.
There's no option setting that will move the sheet tabs to the left. You can send suggestions to: mswish@microsoft.com Put "Excel" in the subject line, so your suggestion will be forwarded to the Excel product managers. Suzy wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Display Data by reference
I Guys, I would like to know how can I display a record from a data base by reference. I have a variable call txtFName, a database’s table call BasicData, and the field from the data base is call FirstName, In txtFname I have stored “FirstName”, my question is how can I display the data I have in FirstName using txtFname. Thanks It has to be in the forms Record Source (query or SQL statement) or use DLookup. -- Build a little, test a little. "Maracay" wrote: > I Guys, > > I would like to know how can I display a record from a data base b...

Data moving
I have many files. Any file has data on column X and special range. I need to a code when run it on any file, open Box similar to following: response=InputBox("Enter new row number") and after i input row number, then copy of data on column X and it's Range, paste to new row number on sheet2. Example for my first file: data on column X has Range (X2:X570),after run code, new row number in Box is 7644,then, copy of data paste to sheet2 and new Range (X7644:X8214). regards You don't say which sheet has the original data in X2:X570 so I'll pick Sheet1 ...

How do I compare data from 2 worksheets to find duplicate entries
I am a novice Excel user. I have to worksheets that have data, some of it is similar (ie. Item #'s, etc.) I want to automatically compare the reports and highlight data on one worksheet that is identicle to the data on the other worksheet. If I can't do this, I'm going to have to manually compare the data. Jack, A coded solution is probably the way to go for your challenge. You can iterate through each item on the first sheet and check to see if it exists in the second sheet before copying it to a new (report), third sheet. -- http://HelpExcel.com 1-888-INGENIO 1-888...

Get external data??
Hi, I have an Access 2000 program that I package with Developer. I currently only allow "Built-in toolbars" and "Menu Bar Only'. However I want the user to have the "Get external data" option under "File". What do I need to allow for the user to get this option? Thanks, -- Phil Phil wrote: > Hi, > I have an Access 2000 program that I package with Developer. I currently > only allow "Built-in toolbars" and "Menu Bar Only'. However I want the > user > to have the "Get external data" option...

vertical text
Any one help me with displaying text vertically, without expanding the row height? Dan, Do you have exceptional eyesight? Bernard "Dan" <hemidan@aol.com> wrote in message news:27b301c38367$5ae76490$a101280a@phx.gbl... > Any one help me with displaying text vertically, without > expanding the row height? ...

Delete duplicate data.
Hi, I have thousands of rows, and each of them contains several columns of data. If I want to delete the duplicated rows, how can I do that? Example, Cust ID Name Desc 001 John sg1. 002 Sam sg2. 001 John sg1. I want to delete the duplicated customer ID of 001? Thanks. Leo Assuming that the entire row is duplicated as in you example you can use Advanced Filtering. Data > Filter > Advanced Filter > Unique Records Only. You can than copy and paste. If the entire row is not duplicated, it's a...

Rename Worksheet From Cell Data
Is there a way to rename a worksheet based on a cell in the worksheet? Running the tiny macro will set the tab name to the value in B9: Sub rNmae() ActiveSheet.Name = Range("B9").Value End Sub Macros are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the macro from Excel: 1. ALT-F...

Letter Templates from Access Data
Is it possible to create letter templates from access DB? If so how. For example I want to create an insurance letter regarding the medical necessity of bariatric surgery. I would need to be in the patients record & would like to choose a template that would place the PT name, comorbitities, etc... into a pre-written letter that would be ready to mail. -- Thanks, Andy The quick response is "Yes" you can do that. One way would be to open Access and in the Help Search field enter the phrase "mail merge". Then, take it from there. Larry "Andy" <sh...