Link certain cells to 2 photos each

R

robzrob

Board Regular
Joined
Jan 10, 2010
Messages
52
Hello

Not sure if this can be done. Anyway...

Some cells in col I of 1st worksheet contain numbers like this: 4063887. For each number in col I there are 2 photos, so, for example, for this number I have photos with filenames:

2012-04-12-CN4063887-1 and
2012-04-12-CN4063887-2

I'd like to be able to select cell 4063887 and then have one keystroke to go to/show 2012-04-12-CN4063887-1 (F1?) and another keystroke (F2?) to go to/show 2012-04-12-CN4063887-2.

I want to keep the photos in the workbook somewhere and also want to be able to put them in the workbook as and when I get them as easily and quickly as possible! (I notice that when I insert a photo into any worksheet in the workbook the filename shows in the Alt Text part of the photo's Properties.)

Cheers
Rob
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Hi,
Just a thought.
Have you tried to create a data validation list that would reference your list and by selecting the code of your choice would return / show the corresponding picture? Although would require a unique code per pic. So it might not apply to what you described.
Willing to show you how if interested to try.
 
Upvote 0
cyrilbrd said:
Hi,
Just a thought.
Have you tried to create a data validation list that would reference your list and by selecting the code of your choice would return / show the corresponding picture? Although would require a unique code per pic. So it might not apply to what you described.
Willing to show you how if interested to try.
Click to expand...

Thanks. No, I haven't tried that. I can see what you mean but don't really know where to start. Perhaps I could get round the fact that each relevant cell will have to link to 2 pics?

Yes, I'm interested.
 
Upvote 0
I can think of two ways:
One involves data validation and a VBA.
The other involves data validation and formulas.

May I ask how many pictures are you planning to add?
How many codes (Column I)?
Would you add pictures in time?
How big are the pictures? (png,jpg?)
Do you want a database?
Where would the picture appear? Column J, K?

Awaiting your reply.
 
Upvote 0
cyrilbrd said:
I can think of two ways:
One involves data validation and a VBA.
The other involves data validation and formulas.

May I ask how many pictures are you planning to add?
How many codes (Column I)?
Would you add pictures in time?
How big are the pictures? (png,jpg?)
Do you want a database?
Where would the picture appear? Column J, K?

Awaiting your reply.
Click to expand...

Probably adding about 10 photos/week
... which would correspond to about 5 codes/week
jpg - up to about 900kb each pic
Not sure whether I want a database, do I? - would prefer everything in one workbook
Pictures could appear anywhere

Thanks
 
Upvote 0
Please try this:
In Sheet1:

Create a sheet with the name you want to use to call for a picture and in a second column the name of the pic as it appears beside the formula bar when you click on the pic. eg: "picture 14"
So your list will look like:
HTML: 
Code	PicName
2012-04-12-CN4063887-1	Picture 1
2012-04-12-CN4063887-2	Picture 2
2012-04-12-CN4063887-3	Picture 3
2012-04-12-CN4063887-4	Picture 4
2012-04-12-CN4063887-5	Picture 5
2012-04-12-CN4063887-6	Picture 6
2012-04-12-CN4063887-7	Picture 7
2012-04-12-CN4063887-8	Picture 8
2012-04-12-CN4063887-9	Picture 9
2012-04-12-CN4063887-10	Picture 10
2012-04-12-CN4063887-11	Picture 11
2012-04-12-CN4063887-12	Picture 12
2012-04-12-CN4063887-13	Picture 13
2012-04-12-CN4063887-14	Picture 14
2012-04-12-CN4063887-15	Picture 15
2012-04-12-CN4063887-16	Picture 16
2012-04-12-CN4063887-17	Picture 17

Do not place any picture here, just the name of the pic and the reference code to call said picture.

In Sheet2:
Place all your pictures here. It doesn't matter where, you can just place them at the bottom on the sheet one over the other, or anywhere you like.
No need to arrange them yet.
in A2 create a data validation list with the following formula as source:
Code:
Code: 
=OFFSET(PicTable,,,,1)
Then type this formula wherever you would like to see the picture example in B22:
Code:
Code: 
=VLOOKUP(A2, PicTable, 2, FALSE)
If your data validation is not in A2, adjust the vlookup accordingly.
Now still in Sheet2 place this vba code.
Tools / Macro / VBE / Insert Module:
Code:
Code: 
Option Explicit

    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub
Save, close VBE.
choose from A2's data validation list a code, and the corresponding pic should appear in B22.

If you prefer a formula I could give you another solution using Index Match.

The con of this solution is that you see the picture always in the same place "B22" but if you organise your file, You could call more than a pic with the value code of A2.

The other con is that it will call a unique picture corresponding to a unique code.
 
Last edited:
Upvote 0
cyrilbrd said:
Please try this:
In Sheet1:

Create a sheet with the name you want to use to call for a picture and in a second column the name of the pic as it appears beside the formula bar when you click on the pic. eg: "picture 14"
So your list will look like:
HTML: 
Code	PicName
2012-04-12-CN4063887-1	Picture 1
2012-04-12-CN4063887-2	Picture 2
2012-04-12-CN4063887-3	Picture 3
2012-04-12-CN4063887-4	Picture 4
2012-04-12-CN4063887-5	Picture 5
2012-04-12-CN4063887-6	Picture 6
2012-04-12-CN4063887-7	Picture 7
2012-04-12-CN4063887-8	Picture 8
2012-04-12-CN4063887-9	Picture 9
2012-04-12-CN4063887-10	Picture 10
2012-04-12-CN4063887-11	Picture 11
2012-04-12-CN4063887-12	Picture 12
2012-04-12-CN4063887-13	Picture 13
2012-04-12-CN4063887-14	Picture 14
2012-04-12-CN4063887-15	Picture 15
2012-04-12-CN4063887-16	Picture 16
2012-04-12-CN4063887-17	Picture 17

Do not place any picture here, just the name of the pic and the reference code to call said picture.

In Sheet2:
Place all your pictures here. It doesn't matter where, you can just place them at the bottom on the sheet one over the other, or anywhere you like.
No need to arrange them yet.
in A2 create a data validation list with the following formula as source:
Code:
Code: 
=OFFSET(PicTable,,,,1)
Then type this formula wherever you would like to see the picture example in B22:
Code:
Code: 
=VLOOKUP(A2, PicTable, 2, FALSE)
If your data validation is not in A2, adjust the vlookup accordingly.
Now still in Sheet2 place this vba code.
Tools / Macro / VBE / Insert Module:
Code:
Code: 
Option Explicit

    Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
    End Sub
Save, close VBE.
choose from A2's data validation list a code, and the corresponding pic should appear in B22.

If you prefer a formula I could give you another solution using Index Match.

The con of this solution is that you see the picture always in the same place "B22" but if you organise your file, You could call more than a pic with the value code of A2.

The other con is that it will call a unique picture corresponding to a unique code.
Click to expand...

Hello

I think I've done it all, but nothing seems to be happening.
 
Upvote 0
Because I am a complete ....
I forgot to include a Named Range.

PicTable is The range of your data table in Sheet 1:

HTML: 
Code	PicName
2012-04-12-CN4063887-1	Picture 1
2012-04-12-CN4063887-2	Picture 2
2012-04-12-CN4063887-3	Picture 3
2012-04-12-CN4063887-4	Picture 4
2012-04-12-CN4063887-5	Picture 5
2012-04-12-CN4063887-6	Picture 6
2012-04-12-CN4063887-7	Picture 7
2012-04-12-CN4063887-8	Picture 8
2012-04-12-CN4063887-9	Picture 9
2012-04-12-CN4063887-10	Picture 10
2012-04-12-CN4063887-11	Picture 11
2012-04-12-CN4063887-12	Picture 12
2012-04-12-CN4063887-13	Picture 13
2012-04-12-CN4063887-14	Picture 14
2012-04-12-CN4063887-15	Picture 15
2012-04-12-CN4063887-16	Picture 16
2012-04-12-CN4063887-17	Picture 17

example =Sheet1!$A$2:$B$18
In your case, use the range corresponding to the size of your table.

so Insert / Name / Define
Type PicTable in "Names in Workbook", then Refers to will be some like =Sheet1!$A$2:$B$18
Then click "Add" and "OK".

=VLOOKUP(A2, PicTable, 2, FALSE) and =OFFSET(PicTable,,,,1) couldn't work because PicTable was not created.



Sorry for the mistake.
 
Upvote 0
Thanks. I'll work on it. (Sorry for not getting back earlier but my PC until recently kept telling me not to go to this site because of a virus risk. Don't know whether that was true, but I kept away just in case!)

cyrilbrd said:
Because I am a complete ....
I forgot to include a Named Range.

PicTable is The range of your data table in Sheet 1:

HTML: 
Code    PicName
2012-04-12-CN4063887-1    Picture 1
2012-04-12-CN4063887-2    Picture 2
2012-04-12-CN4063887-3    Picture 3
2012-04-12-CN4063887-4    Picture 4
2012-04-12-CN4063887-5    Picture 5
2012-04-12-CN4063887-6    Picture 6
2012-04-12-CN4063887-7    Picture 7
2012-04-12-CN4063887-8    Picture 8
2012-04-12-CN4063887-9    Picture 9
2012-04-12-CN4063887-10    Picture 10
2012-04-12-CN4063887-11    Picture 11
2012-04-12-CN4063887-12    Picture 12
2012-04-12-CN4063887-13    Picture 13
2012-04-12-CN4063887-14    Picture 14
2012-04-12-CN4063887-15    Picture 15
2012-04-12-CN4063887-16    Picture 16
2012-04-12-CN4063887-17    Picture 17

example =Sheet1!$A$2:$B$18
In your case, use the range corresponding to the size of your table.

so Insert / Name / Define
Type PicTable in "Names in Workbook", then Refers to will be some like =Sheet1!$A$2:$B$18
Then click "Add" and "OK".

=VLOOKUP(A2, PicTable, 2, FALSE) and =OFFSET(PicTable,,,,1) couldn't work because PicTable was not created.



Sorry for the mistake.
Click to expand...
 
Upvote 0
cool no problem
better safe than sorry.
 
Upvote 0
You must log in or register to reply here.

Similar threads

melodramatic
  • Question
Vba - insert photo with varying sizes
Replies
1
Views
344
Micron
M
B
  • Question
Looking for a way to automate scheduling duos into different squads
Replies
0
Views
184
bsweet0us
B
D
  • Solved
An efficient way to colour a range of cells on a line with a number or another way (suggestions welcome!!)
Replies
7
Views
371
Peter_SSs
Peter_SSs
8
  • Question
How to pop up an image on another worksheet?
Replies
9
Views
1K
NdNoviceHlp
NdNoviceHlp
G
  • Question
Links to cells in other Workbooks occasionally change the WORKSHEET reference. Why?
2
Replies
11
Views
252
GTS
G

Forum statistics

Threads
1,216,522
Messages
6,131,146
Members
449,626
Latest member
Stormythebandit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top