Indirect function in a picture

Hello everybody ! My new job requires me to do some fairly complicated (at least for me) excel spreadsheet and decided to join this forum to learn a little bit more about Excel.

I am trying to make an if condition for a picture (ie, the picture will change depending on the product)

I am using the camera option, and if I write as a function on the picture =Sheet2!$A$1 it is returning me the picture of that cell (everything works so far)

But when I write Sheet2!$A$1 on cell Sheet1!$P$15 for exemple, and then write in the picture formula =INDIRECT(Sheet1!$P$15), it is giving me the following error:

The text you entered is not a valid reference or defined name.

I am using Excel 2010

Any idea to the problem will be greatly appreciated.

Max Otis

EDIT: I attached the Excel sheet Cut list.xlsx

Hi Friend,

Follow below steps to get the desire result.

STEPS:

1. Change the pictures name as below

Standard_Shaft
Standard_Machined1
Standard_Machined2
Standard_Suspended
Weigh_Roller_Machined_Shaft
_102Standard_Shaft
_102Open_Ended
Open_Ended


We have changes the picture name so that it can supported by Picture 6 in TroughRoller sheet

2. Create Names by Names manager of above
3. Bring a ComboBox1 from ActiveX control of Tool bar and enter below code

Private Sub ComboBox1_Click()

On Error Resume Next
    ActiveSheet.Shapes("Picture 6").Select
    Selection.Formula = ComboBox1.Value
    
End Sub

4. Update its ListFillRange properties as Q14:Q21
5. Before clicking the combo box be sure that it is not in Design mode
6. Click the combo box to see the changes

See attachement.

If you are Ok with the procedure then update the status to SOLVED.

Regards
taps


Hi,
Note that, the same can be done by CHOOSE funtion, but it is not good if the number of picture list is too many say, 20/30/40 etc.
Otherwise CHOOSE funtion can be used.

I would suggest a simpler approach... using a named range and moving the formula into the properties of the named range. Then just use the named range as your reference.

The formula for the picture becomes

Formula:

=ShownPicture

and the formula for the named range (ShownPicture) is

Formula:

=INDIRECT("Sheet2!$A$"&VLOOKUP('TROUGH ROLLER'!$P$14,'TROUGH ROLLER'!$Q$14:$R$26,2,0))

abousetta

Hello javier,

I don't have Excel 2011 for Mac so I can't test there but make sure you create a range name as I described. If you're still having problems then please start a new thread and make sure to note that you are having trouble with this in Excel on Macs. There may be fine differences that I am not aware of between the two versions.

abousetta

javier...
Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.