Embedding a chart in a cell?

[Leumas786]

I am curious, is there a way to make it so the last column in a series of data could have a drop down arrow of sorts where when you click it, it displays a chart for the row you are on.

I have a sheet with about 400 rows and each row has data that needs to be plotted on a chart. Instead of making 400 sheets with each chart I was trying to come up with a more manageable solution. Anyone have any ideas or know if this is possible?

[ChemistB]

I attached two options. In Option 1, there's a dropdown list of your ID's (series IDs) and then INDEX to fill in the rest of the values.

In option 2, closer to what you asked for, at the end of each row is a dropdown where you can select "Y" and that row will be graphed. In this option, you could hide rows 2 and 3.
Do either of these work for you?

[Leumas786]

This is VERY helpful, I think option #2 will work out perfectly. My only lingering question is, how do you format the column you have titled "Chart?"

View the cells gives me no insight into what you have going on in that cell. I thought it might auto create the dropdown menu when I put the info into my spreadsheet but it didn't.

One more thing, I figured that I can just type whatever I need in the cell to make the specific chart appear but the chart system is not locating the numbers. It is displaying the "#NAME?" error. What could be causing this. Are there any tricks to applying this method that I may be overlooking?

[Leumas786]

So it seems that my problem is that the ID does not allow for a complex entry. I have names with numbers after them for example "Alvin 33-08" and it does not seem to be able to work with an entry like this. I guess I will have to reduce it down to something simpler or put in a arbitrary value

[ChemistB]

for the "Chart?" column, I just selected the column and set up Data Validation (Data Tab>Data Validation> List> Y)
Where is it displaying #NAME?

Here is a good link for learning the INDEX function
http://www.contextures.com/xlFunctions03.html

[ChemistB]

The complexity of your ID shouldn't matter. Can you upload a spreadsheet example (Go advanced>Manange Attachments)?

[Leumas786]

for the "Chart?" column, I just selected the column and set up Data Validation (Data Tab>Data Validation> List> Y)
Where is it displaying #NAME?

Here is a good link for learning the INDEX function
http://www.contextures.com/xlFunctions03.html

It is displaying "#NAME" in the what would be the cells directly under the Val 1-Val6 (The ones that are used to construct the chart)

I think the problem lies in the ID column, I tried several different things and if anything other than a letter is placed in the ID column it does not work. Is there any way around this or am I limited to only 26 possible values?

[Leumas786]

The complexity of your ID shouldn't matter. Can you upload a spreadsheet example (Go advanced>Manange Attachments)?

This might help, its an exert of the data

Example.xlsx

[ChemistB]

Yep, that helped. I created a defined name called "List" which is a list of your ID's. You're using that in the formula instead of writing out the full range (i.e. =INDEX($B$6:$G$27,MATCH($A$2,List,0),COLUMN()-1) instead of =INDEX($B$6:$G$27,MATCH($A$2,$A$6:$A$27,0),COLUMN()-1))

To create a simple defined name, Select your range (i.e. A6:A27) and in the name box (the white text box upper left where you usually see the Cell ID) type in the defined name (i.e. in this case List) and hit enter. To look or modify your defined names in Excel 2007, go to the Formula Tab > Name Manager.
Did that fix everything?

[Leumas786]

Yep, that helped. I created a defined name called "List" which is a list of your ID's. You're using that in the formula instead of writing out the full range (i.e. =INDEX($B$6:$G$27,MATCH($A$2,List,0),COLUMN()-1) instead of =INDEX($B$6:$G$27,MATCH($A$2,$A$6:$A$27,0),COLUMN()-1))

To create a simple defined name, Select your range (i.e. A6:A27) and in the name box (the white text box upper left where you usually see the Cell ID) type in the defined name (i.e. in this case List) and hit enter. To look or modify your defined names in Excel 2007, go to the Formula Tab > Name Manager.
Did that fix everything?

Awesome, this worked perfectly. Thank you so much