Select & Show One Chart from Many

Chandoo,

you don’t have to insert a picture to create an object for ‘getChart’. Excel has a tool for this called Camera Object. It is not available via the Excel menus (and it is not documented in the help files). You first have to add the icon to one of your toolbars. Right-click on one of your toolbars and select customize and the commands tab. From tools, select Camera, and drag it to the toolbar. Clicking on the camera object takes a snapshot of the cell range you selected. You can then replace the cell range (the camera is linked to) by your range name ‘getChart’.

The Camera Object is a great tool for creating dashboards. Charley Kyd over at http://www.exceluser.com provides a lot of information and tutorials about this technique.

It is my pleasure to know your website. Thanks. Sam RYU

Chandoo -
 
Nice post, nice little trick. Internally it works just like the Camera Tool, except the camera tool by default applies a white background and black border to the shape containing the picture.
 
If you’re showing a picture that may change dynamically, it’s the only way to go. The problem is that it sucks down resources, and using it a dozen times on a fancy dashboard report may result in a sluggish interface. Since it’s only chart data that’s changing, there’s an easier way to make this work, and I’ve gone ahead and posted it on my blog in Display One Chart Dynamically and Interactively.
 
Thanks as always for the inspiration.

@Jon
Nicely done. (I was thinking about the same approach, but using offset formula and named ranges so that the source data range could change and the graph would update automatically.)

@Chandoo
There seems to be a problem with dynamic pictures when it comes time to printing - that is pictures created with the camera tool or with [Copy Picture] which have a live formula behind them (kind of like what you have there). I can’t recall the exact details of the print problem - except the the layout gets screwed up - but it goes away when you remove the formula from the picture. My workaround has been to write a small piece of vba code which sets the formula for the picture (forcing the image to change) and then deleting the formula from the picture again to leave it ’static’. I am sure you can imagine how to write that code from the change in a dropdown selection! (in fact you have already written it, I expect)

@Alex
 
I used to always use offsets and named ranges, and scoff at such simple approaches as the one I posted about. But as I mature, I’ve learned that reliability and maintainability trump elegance every time.
 
The problem with Camera Tool objects is that sometimes the formatting of the object shown in the dynamic picture may be completely trashed. This sometimes seems to bee caused by one of my favorite culprits, that is, a printer driver. I don’t recall the details, but I worked with another expert to try to fix such problems a couple of years ago, and updating the printer driver made the problem go away.

Chandoo -
 
Circular references certainly have their place, but not in a workbook designed for general purpose use. People don’t know what the error means, they become alarmed, and they don’t use the workbook, because they think it’s broken.
 
Whenever I’ve tried using circular references on purpose, even following someone else’s cookbook approach, I get tangled up. They are remarkably hard to develop, debug, and use reliably.

By the way, it’s not necessary to use a single cell for the linked picture trick. You can use any contiguous rectangular range.

A very nifty trick. I’ll start using it today!
I think one key advantageof this technique over a single dynamic chart is that you can easy switch chart types.

Oh Mr Pointy Head Dilbert! - this is just a brilliant BRILLIANT tutorial. Many thanks indeed. I will put your tutorial into practice as soon as possible. Bye for now Mr Chandooooo.
denise

Hi Chandoo..

This is simply great as usual. However, i need ur help to resolve one issue. Its that I have around 23 charts to display in a particular place but “IF” command can be used limited times (i guess around 7 or 12).. can we use anyother formula instead of “IF” or do you have any other solution??

Cheers

@Chandoo— it is fundoo idia…
@John— thanx for giving hint of camera..

I had used one more tool– the horizontal scroll bar for changing the slides — like in power point…

Enjoyed !

Waseem -

Put your list of items to choose from in a column (e.g., E1:E23). Use this range in the list source of the data validation for the cell containing the selected chart. Use this for the Refers To formula for getChart:

=OFFSET(’Place your charts here’!F10,MATCH(’View them here’!$C$2,’View them here’!$E$1:$E$23,0))

@ Jon RE: Offsets and Named Ranges
I’ll concede the simplicity/maintainability factor.

Unfortunately, I find myself in the mode of developing tools for users who won’t understand even the most simple mechanisms. I am trying to develop functionality which is to some degree “user-proof”, so things like dynamic extensible ranges are crucial. Guess I’m stuck in that mindset.

Hi,
A little of topic, but, thinking about dashboards / KPI reports you could use indirect to reference images or drawing objects, or even create the plot matrix thingy (from last week I think) using smileys or some other icon set.
Good for corporate division logos on standard reporting format spreadsheets too.

@ Chandoo & Waseem==

Use one counter cell and offset command. Using these, one can enjoy ‘n’th no. of slides…

Try it ! Gud Luk !

Chandoo great post really helpful. Do you have a youtube type video tutorial for this as it is a rather complex trick?

Any instructions on how this should work in Excel 2007? I’ve fumbled along but I can’t get it to work. I’ve even downloaded the example spreadsheet which works in 2007 but I still cannot create one. A couple of things:

- There seems to be some data validation in the example spreadsheet but no mention of it in the instructions.
- For some reason, I cannot access the formula bar when I have the picture selected in my work. I can in the example spreadsheet which I find odd.

I really want this to work since I have several spreadsheet that this would be perfect for.

Brodiemac,

with Excel 2007 use Insert|Object instead of Insert|Picture. Everything else works as Chandoo described in the post with Excel 2007 as well.

@Chandoo: “Unfortunately I do not have excel 2007 to replicate your problem.”
 
Unfortunately?? I’ve spent about a day and a half this week trying to make a chart that works fine in Excel 2003 work at all in 2007. It’s been a process of:
 
1. Hmm, that doesn’t work.
2. Let’s try this instead.
3. Good, now it works, but…
4. This doesn’t work either.
5 Go to 2.

Among the problems, if you’re following t home:
1. In Excel 2007, XY charts don’t work very well on charts with line chart date axes.
2. In Excel 2007, ActiveSheet.Shapes(ActiveSheet.Shapes.Count) isn’t the last shape you’ve added.
3. Some complicated Excel 2003 charts lose their custom (linked to cells) data labels when opened in 2007.
4. In a complicated Excel 2003 chart, which has two 4-point area chart series to highlight a background range, and an XY series with custom markers (pasted shapes) and more then 4 points, only the first four points appear with their custom markers, though the code that applies the markers does not fail, and the data labels for the marker-less points do in fact appear.
 
And those were just the issues from this morning.

@Robert “with Excel 2007 use Insert|Object instead of Insert|Picture. Everything else works as Chandoo described in the post with Excel 2007 as well.”

That worked, thanks.

@Jon Peltier “=OFFSET(’Place your charts here’!F10,MATCH(’View them here’!$C$2,’View them here’!$E$1:$E$23,0))”

I still can’t get this to work. Excel complains that it’s missing the Cols parameter.

Chandoo, don’t be scared of 2007. I really like it a lot. The menu structure takes a few hours to get used to but now that I know it as well as previous versions, I can actually get things done much faster now. Trying to follow instructions written for older version can be a bit confusing though (this page, case in point).

Hi Mr PHD,

I attempted to make some changes to strive for a solution which is more “elegant”… please check your mailbox.

However, it’s still a neat trick - naming charts/images as a range…

brodiemac -
 
Try this. I posted without testing. If I’d tested, I’d havee caught the eproblem in two seconds and fixed it in three.
=OFFSET(’Place your charts here’!F10,MATCH(’View them here’!$C$2,’View them here’!$E$1:$E$23,0),0)
 
BTW, I’m not at all scared of 2007, but it frustrates the you-know-what out of me. It will seem to be working fine, and I’ll forget that it’s a different animal than 2003. Then one day I’ll have three or four hours when nothing goes as expected, nothing goes the way it used to, things are upside down and backwards.
 
Chandoo - I’m far from Seattle, at the other end of route I-90 almost in Boston. But I’ll fill in the sound of laughter.

Firstly thanks Chandoo for such a great site.

Trying to create a dashboard from multiple data sources. Had problems with the charts as pictures resizing on print preview. Anyone with a workaround or suggestion as to how to overcome?

Thanks.
J.

Hi Team,

I’m trying to call more than 4 charts by using the If condistion in reference, but it’s not working. Is their any alternative way for call more charts

Hi Team,

Is their any alternative way to call more than 4 charts.

Thanks

Venku

Using Excel 2007, I need a chart/graph that has the following - looks like an upside down T:
x- axis, numbered from left to right (both sides positive - no negative; represents the number of drops of a solution added to an unknown buffer; left side 10 to 0 is NaOH; right side 0 to 10 is HCL) 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10; y-axis (pH) starting at 0 and going up to 14 (no negative values). I can graph a single titration curve on a graph but don’t know how to do both and label them as directed (the upside down T). I am told that this is possible but the professor will not demonstrate it.
Thanks.

Hi Chandoo!

I love this page… very interesting stuff!! And great job of explaining things…even I got through it alright

So, like a few others, I have quite a few graphs to show (13). I’ve gotten everything to work smoothly for a a portion of the graphs, but now that I’ve added more graphs, I realize that I’ve reached my “If” limit.

It seems like the offset thing would work well for simple graphs, but my graphs are damn complicated (3D and all sorts of funky aside from that) so I think it would take months to get all the different characteristics of the graphs to show up correctly.

Could you help me work out the one idea you had: “instead of naming the charts in data validation as Sales, Expenses and Profits, you can name them as 1 sales, 2 expenses 3 profits.
Then you can extract the number using left() and find() and feed this to indirect().” Maybe that would be able to work for me? Any help/explanation is much appreciated! Or maybe you have another idea?

~eq

Dear Chandoo,
Thank you for this trick. Actually this helped me in different way. I look for some ideas to make some thing like a photo database. Beacuse I’m handling so many styles details (garments) in my office. I had prepared a workbok in Excel. Second woksheet using as a databse. First sheet contais a page like report. I’m using Vlookup() to display the deatils. When I enter Style No in the cell it will display balance deatils. But I didn’t had the ideatto display the picture of the styles with the details. Now it is possible. Thank you again.

Best Regards,
Maikkil Mathew

Dear Chandoo:

I am also having problems with printing the dynamic chart. In my case, I either want the chart to be seen or not seen depending if I choose a certain population.

I have screen shots: the original with the chart sized appropriately, the print screen menu, and the print screen look (which when I go back to my spreadsheet the graph is also changed as well). How do I send them to you?

~fh*

Dear Chandoo,
Vlookup() not working with this trick so I add one more column to the Database Eg: like this
—– A——-B————C————-D——————E——–
-1–ID—-Name—-Phone No.—-Photo———–Photoref
-2–11—-Rose—-2345324——@@@—–=cell(”Address”,D1)
—————————————————————————–
Photo column contains photos and Photoref Column contains formula “=cell(”address”,D1)” for first Row and copy the same in entire column.
With the help of Vlookup() displaying all other details in first sheet. If enter IDNo. in the cell A1 it will show Name and Phone No.

one hidden / white text cell in any corner (here H10) contain formula =”’sheet2′!” & VLOOKUP(Sheet1!$a$1,range,5,FALSE) . Range is the name of range contains data in sheet2.
Now this cell (H10) shows —– ’sheet2′!D1— like this. It will change with the change of the ID No. in A1

Name a this formula “=INDIRECT(Sheet1!$H$10)” as “GetPic”

Now as you did, insert and select a picture and type in formula bar “=getpic”

Now the Name, Phone no and Photo will change with the IDNo in A1

I think this will help somebody looking for a photo database in Excel like me. And I think now this way will handle thousands of charts with the help of Chart ID in another column.

Best rgards,
Maikkil Mathew

Hi Chandoo:

Ok - I’ve uploaded four photos: original, print preview menu, print preview, and then after print preview to showing the “scrunching” of the chart.

Any suggestions?
Thanks
~fh*

Dear Chandoo,
I’m very happy to hear that you are trying this yourself and share with others. Because I couldn’t upload all details such as screen shot of the worksheet etc. If you provide your email then We can give the details also. You don’t need to thank me, because this is your idea I used in diferent way thats all friend.
Thanks and Best Regards,
Maikkil Mathew

Hello,
Thank you for this information. I have more than 3 charts to show so having problems in the refer to section (formula too long) - not sure how to do index() and offset(), can you help?

Instead of having to select an option from the pull down menu (expenses, sales, profits), is it possible to have a link do the same task? For example, In my spreadsheet I have a list of Departments. I want to be able to click on a department from that list and have it display the appropriate chart. Is this possible?

Ryan -

You could do this a few ways. I might choose to use a listbox instead of a dropdown, so all of the options are visible without having to pull down the list. Otherwiase it works the same.

Instead of using official links, you could use a Worksheet_SelectionChange event procedure to detect when a cell was selected, and recreate the charts with that cell’s data.

Dear Chandoo, I could not manage to implement this lession properly. Could you please teach me in a simple way. I am sorry I couldnot implement it.

Hi,
I using your ‘Select & Show One Chart from Many’ chart and experience a problem when adding more charts.
The indirect worksheet formula doesn’t allow me to ad more into the ‘refer to’line - Is there a restriction in data entering?
as example:
=IF(’View them here’!$C$2=”Sales”,INDIRECT(”‘Place your charts here’!F11″),IF(’View them here’!$C$2=”Expenses”,INDIRECT(”‘Place your charts here’!F12″),IF(’View them here’!$C$2=”MFG”,INDIRECT(”‘Place your charts here’!F14″),INDIRECT(”‘Place your charts here’!f13″)))
can you help me
Martin

I have done everything to get select & show to work, but I can’t get the graph to show up. The formula is correct, but it wont let me put anything in the formula bar when I select my picture. I’m using excl 2007.

Thanks for any help

I reaaly do appreciate the info. If I had read all the previous info I would have seen the same thing you wrote to someone else. That work great.
I have about 33 graphs. Will I be able to get all this in 1 formula?

Thanks again the help