Camera Tool & Indirect Function

[Pondcroft13]

Dear All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I am currently trying to use the camera tool in excel to display different graphs depending on what a user has selected in a drop down window – drop down window created using forms tool.
<o> </o>
I have named ranges behind all the graphs I wish to use and when I type the named range into the formula bar when I have the ‘camera window’ selected, eg =cht1 it works perfectly, and displays cht1 in the window. Due to the fact I am using a drop down menu however I want the ‘camera window’ to refer to a specific cell where the contents of the cell will change dependent on the selection in the drop down menu. Unfortunately when I try and do this the ‘camera window’ displays the contents of the given cell, eg the text ‘cht2’ rather than the range of cells and therefore the graph that this ‘name’ actually refers to.
<o> </o>
I am presuming I need to use some kind of INDIRECT function but I have tried various different combinations of this function and have not been having much joy. Any ideas?
<o> </o>
In summary:
<o> </o>
I have created 7 charts, the cells behind all these charts have been given range names as follows cht1, cht2, cht3 etc etc.
<o> </o>
I have created a ‘window’ using the camera tool that will show and resize anything it is pointing to.
<o> </o>
I have 2 cells (B34 & B35) containing the data required to choose the correct range name when a selection is made by the user. The first cell (B34) contains a numerical value (1-7) depending on what the user selects in the form drop down window. The second cell contains the following formula:
<o> </o>
=IF(B34<7,"cht"&B34,"cover") – FYI – ‘Cover’ is a named range that shows a picture if a value selected is false.
<o> </o>
The above formula/cell will then return a value cht1, cht2, cht3 etc etc – it is this cell that I would like to link the ‘camera window’ to, as the above returned value/s are the named ranges that will display my charts. Unfortunately the ‘window’ is currently displaying the text values rather than the ‘named range’ and therefore the graphs that these values align to.
<o> </o>
Hope that makes sense! Please help!

 

[Charles Crous]

Hi,

You've probably figured it out by now,but just in case you haven't:
I think it has to do with your name choices. Excel 2007 has more columns and rows than Excel 2003 did, so cht2 actually refers to an actual cell. If you add another vowel or consonant to your range names, you should be fine.

Regards,

C