Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading Pictures from a table (Excel 2000)

    Hello,

    I want to use the lookup command to look at a table of named pictures and bring them over to a cell.

    Rough Example of the formula I use but that does not work.

    =If(A1>0,Lookup A1, "List of pictures",0 )

    What I want to do, is have a number of pictures in table. If the value of A1 agrees with the picture, I want that picture to show up in the cell area. I will have about 30 pictures.

    I can not get a picture to be in a cell location, so that makes it hard to do. If I could get a pictue to identify to a cell, then I could do it.

    Hopefully you can understand my ramblings, if not let me know.

    Thanx,

    Jerry M

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I'm not sure whether you've looked at this thread. It could give you a start. HTH
    Gre

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I think I'm beginning to understand what you're trying to do. This table that you want to do the lookup on - does it actually contain the pictures or does it just contain the names of the pictures, which are then located elsewhere? Is it possible to attach a sample workbook - file size and sensitivity permitting?

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Jerry M

    If you look at this posting , you'll see a description of how to create watermarks on a page. Go down about 1/2 in the posting to get to the info that's probably most relevant to your situation. There's some macro code there - here's the useful piece of it

    ActiveSheet.Shapes("WordArt 1").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste

    (The rest of the code just repeated the paste a few times.)

    The code takes a piece of WordArt that had been created (not part of the macro), copies it to the clipboard, selects a cell, and pastes the clipboard contents AT that cell. Note that I didn't say IN that cell. As it seems you've observed, objects like WordArt aren't really associated with a cell - they float on another layer above the cells (a Word metaphor).

    What I'm not sure of from your posting is the IF statement, which has 4 parts instead of the usual 3 (test, action if true, action if false). I'd assume the 2nd and 3rd items really go together for the action on true if a1>0.

    I'm not sure how to do what you want without resorting to VBA. Others who've already responded (Unkamunka and Brooke) are probably much better than me at that. But it seems the general steps are:

    preliminary step - not part of the VBA you'd want:
    - identify the shape names (like "WordArt 1" above) corresponding to your pictures; I don't know an automatic way of doing it. What I would naively do is go to the sheet where the pictures are, turn on the macro recorder, and then select each one to see their "VBA shape name". If you're inserting the picture from a file (as I suggested as an alternative in my posting), you need the file names.

    main part - pseudo VBA
    <pre>If Range("A1") > 0 then
    lookup A1 in list of pictures
    'not sure what you mean by that -
    'what is in the list that you're matching A1 with?

    'not sure what sheet your pictures are on;
    'you may need to activate a sheet of pictures before the next step
    ActiveSheet.Shapes("matched picture name").Select

    Selection.Copy

    'you may need to switch back to the sheet where you want to do the paste
    Range("B3").Select 'or wherever you want the picture
    ActiveSheet.Paste
    End if
    </pre>



    Hope this helps a little.

    Fred

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I tried a sample table to send. But it exceeds the limit allowable. Any advice, or can I send it directly to your email address?

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Have you tried putting it in a zip file?
    Gre

  7. #7
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Even If I zip it, it says file to large. Any other ideas?

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Reading Pictures from a table (Excel 2000)

    How big is it?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Here is how you can do that.

    Assumptions:

    - your pictures are in ONE cell each, in column B of sheet1
    - your lookup values are in column A, next to the pictures.
    - the code to show the picture of is in cell C1

    Define this name:

    Name: Picture
    Refers To:

    =INDIRECT("Sheet1!$B" & MATCH(Sheet1!$C$1,Sheet1!$A$1:$A$100,0))

    Now create an empty picture:
    - Select a cell with any picture
    - Hold Shift, press Edit, Copy picture
    - go to the place you want it to show up
    - select Edit (while holding shift again), Paste Picture
    - clcik on the picture and then click once in the formula bar
    - type this into the bar:
    =Picture
    press enter.

    Now change the code in cell C1 and see what happens.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Jan, thank you for your help, but I am still am not there, but I am close.

    Even tho it is a small sample file, I still can not upload it.

    I must not be doing something right because I can not get it to work.

    A few questions.

    I may be reading your assumptions wrong.

    I have three pictures (in my sample) they are in B1,B2, B3. (Actually they hover over these cells)
    What do you mean by lookup values in Column A? Do you mean my lookup formula, Or just a number?
    The code to show the picture of is in cell C1. Do you mean just a simple number? I am sorry that I am so confused.

    Thank you for all your help,

    Jerry


    I

    I

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I assumed this situation:

    You have two columns.

    Column A contains some sort of code (number, text, whatever) that uniquely determines the picture, which is in column B. In cell C1 you type (or select, using a dropdown) a code or number which should exist in column A. The formula I suggested will now extract the cell address of the picture that belongs to the row of which the information in cell C1 matches the info of column A.

    So if C1 contains
    "ThisLine"

    And A5 contains "ThisLine", then the function in the defined name called Picture will yield a reference to cell B5.

    Could you post just a small portion of the file, e.g. delete all rows beyond row 10 and post that zipped?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    Jerry,

    Sorry about the slight delay. This is as small as I can get the file - I've saved the pictures as gifs as I think that gives me the smallest file size. You'd probably want to move the lookup source out to another sheet but this should get you started. The problem I see is that you need a seperate defined name for each line/picture, unless there is a way to get the row that the picure sits in. Maybe excel4, anyone? So if you have lots of rows on the form then you may want to add the names by code. Take a look and see what you think. Any questions, just shout.
    Attached Files Attached Files

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I assumed there was alimited amount of "auto-changing" pictures.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Pictures from a table (Excel 2000)

    I don't know. My impression was that this would be used for an invoice-type scenario where the maximum lines and hence lookups you'd get would be thirty or so, but I presume the range of products to do the lookup on could be quite large. If there were only thirty auto changing pictures, then I'd add the names manually. Over that and I'd probably start thinking of code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •