Chart data with row selection

[cas8100]

Good morning. I'm stuck and am hoping someone may be able to help.

I have a database in spreadsheet form with each record saved on a different row. I will be using the data in each row to create a bar graph / timeline.

What I am trying to do is add a "row select" feature on the chart that would allow me to select which database entry (row) Excel used to create the chart. I would like to be able to change this selection freely so that I can analyze each database entry. I assume there is a VBA solution. I just can't seem to find any examples online. Thanks in advance for any suggestions.

[ChemistB]

Here's an example from Andy Pope using data Validation, MATCH and INDEX function. The value is A7 is selected using Data Validation Dropdown Box, In A6, the MATCH function finds out what row that row of data would be in.

Please Login or Register  to view this content.

In B7:M7, the INDEX function returns the appropriate value

Please Login or Register  to view this content.

Does that work for you?
Notes; You can change the font color of A6 to white to not see that number or you can use a FORM dropdown box over A7 with a link to A7 to hide the number returned.

[cas8100]

Thank you! That is exactly what I'm after. Is there a way to put that drop down box on the chart itself so that I don't have to reference (display on projector) the spreadsheet containing the data?

CS

[ChemistB]

Yes, In Excel 2007, select your chart and go to the Layout Tab of your ribbon. There's an option for Data Table >Show Data Table. Does that do what you want?

Note, that shows your data but doesn't allow you to modify your choices. Hmmmm. I would move the chart to a worksheet rather than in a standalone sheet and then have that data/functions directly below the chart. You can format the rest of the spreadsheet to not show gridlines.

[cas8100]

Well.... I'd more like to just move cell A7 (in your example file) to display only that cell and its associated drop down box on the chart itself. I would make the chart its own tab in the workbook so that I don't have to display any of the tabular data.

[ChemistB]

You can go with a Forms List Box;
Go to your chart tab and on the ribbon> Developer (if it's not there, you need to go to Excel options to make it visible)>Insert > Forms List box. Then create a Forms List box on your chart and right click on it and Format Control. Fill in the correct ranges. Does that work?

Edit: I updated my original attachment to work this way. Take a look.

[cas8100]

Man....have to say you guys are pretty good. Thanks very much.

[ChemistB]

Don't forget to mark this thread closed if you're satisfied with the solution.

[cas8100]

I would....but I'm not exactly sure how to do that. Didn't know that was forum etiquette.

[ChemistB]

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save