• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting on figure from many to several entries

aires

New Member
Following the advice posted at http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/, imagine I have hundreds of calculated fields resulting in a name, and I want for each row to have a figure correspondent to that name.


The technique of attributing a getChart formula to an image (or the Camera Tool), as described, works only if the function getChart has only absolute references (p.e., $A$2), not working if it has one relative reference (p.e., A2) - which would allow me to paste the image/camera several times in front of the names, and it automatically updating for everyone. Does anyone knows how can I handle that?


Best regards, Aires
 

Hui

Excel Ninja
Staff member
Aires,


A few comments if I may


1. The camera tool in Excel 2007 is quite happy to have a =B7:C8 formula without Absolute $ sign

2. The whole point of the Camera tool is that it is returning a snapshot of somewhere else on the spreadsheet, and as such it has no reference to where it is actually located. So copying and pasting to different areas will always give the same snapshot regardless of whether =B7:C8 or =$B$7:$C$8 is used.

3. Search and replace doesn't work inside the formula bar of the Camera Tool or Picture Formulas

4. It may still be worth going to the trouble to paste all the Pics or camera tools you need, as once pasted and formulas are manually edited, you can :

a) Size them using Alt Snap (Alt Drag) to fit the cells exactly

b) You can align them and make them the same size by selecting them all and using the Page Layout tab, which has Grouping, Alignment and Sizing tools

c) You can group them and move them as a whole

d) Just noticed that Pictures have a Tab property ie: You can press the Tab Button to jump between them, but the Camera Tool doesn't ?
 

aires

New Member
Hui,


Thanks a lot for the comments. My main problem is that the image labels are dynamic (change from month to month). So, it's not possible to refeering the images manually, since they're going to change often, and the reference should reflect that - I'll not be in control of the spreadsheet later.


Is it possible to define the function that receives parameters? Then I could create only one function, and refer manually to a cell (the cell that's hosting the final image, for instance) as a parameter (even if I had to input initially the refs manually).


Regards, Aires
 

Hui

Excel Ninja
Staff member
Aires

Are the images imported from somewhere else?, Can that application change the names into a known format or system?

Are the pics located in specific Cells every month ?

ie: is a Picture of Item 1 in C1 every month even if item 1 has a different name month to month
 

aires

New Member
Hui,


OK, let me describe the scenario.


I have, say, 100 products, and each product has a logo. On one sheet, I have 100 lines, with the logo image on A1:A100, and the name of the product on B1:B100. On other sheet, through some calculations with other sheets as well, I have dynamic results about the top 20 products sold (by period/by salesperson/by region - I can choose my preferences through lists), and on that list I want to put logos aside the product names (but I can't know a priori which products will appear, because that's due to the preferences I chose, and the results of the sales team).


I can move freely the logo images to whichever format/position needed - I currently have them as .PNG's, local stored. You can assume the product names don't change, and that we won't add new products (because a change on that I can handle manually).


Does that answer to your questions?


Thanks a lot for your time and patience.


Best regards, Aires
 

Hui

Excel Ninja
Staff member
Aires

Lets try this


I have assumed that the Logo and Descriptions are on a Page "Catalogue"

With titles in Row 1 of Pic and Product

The Pictures/Logo's are in A2:A100, Product names are in B2:B100

All pics are the same size and snapped to the cell edges using Alt drag


I have setup a dynamic name Product

=OFFSET(Catalogue!$B$1,1,0,COUNTA(Catalogue!$B$2:$B$5000),1)

you can reduce the 5000 if you want


On another page I have setup a lookup cell with a Data Validation Type List with list =Product and I have named this cell "Look"


Next to The Look Cell I have put an Autoshape Square, which is snapped to the cell and the cell is the same size as the pictures on the Catalogue Page in Column 1.


I have now setup a named range "getPic" with formula

=INDIRECT(+CONCATENATE("Catalogue!$A$",1+MATCH(Look,Product,0)))


This gets the Position of the Validatation from the Look cell and adds it to the Catalogue page name to make a Text value, which Indirect can use.


Now go back to the Picture next to the Look cell and click on it and then the formula bar, in the formula bar type =getPic


You will now have a picture dependent on the value of the "Look" cell


For your example where you have say the top 20 product, you may need a number of getPic named ranges ie: getPic01, getPic02, getPic03 .. getPic20 etc

each will be based on a different lookup value dependant on how you arrive at your top 20 salesmen/products etc.


This works, except that you need to manually maintain the picture next to the Product name on the Catalogue Page, You can move them up/down, but do it together.


I am happy to email this example to you if you email ihuitson at gmail dot com
 
Top