Jump to content
Developer Wiki and Function Reference Links ×

Worksheet image with "if" statement


DomC

Recommended Posts

Hi

Maybe not the right forum for worksheet formula questions.

I am searching a way to insert an image in a worksheet cell by using a formula. I already searched the forum but could not find any related questions.

What I am trying to do:

If want to show an image in cell D1, if the cell A1 has the value greater than "0".

=if((A1<>0), 'here i want an Image', 'false')

I tried this:

=IF((A1<>0), (IMAGE(N='k1')), '') #-> cell is empty

=IF((A1<>0), D1, '') #D1 is refering to the cell "D1" which shows the image by =IMAGE(N='k1')

=IF((A1<>0), IMAGE(N='k1'), '') #-> cell is empty

also tried from other direction:

=IMAGE((IF((A1<>0), "N='k1'", ''))) #-> error because of the double quote I think

=IMAGE(E1) #Simple test, if IMAGE() formula can use values from other cells

I give up... Any tips?

Edited by DomC
Link to comment

One step nearer to the solution ... anything goes with script, forget crampy worksheet formulas.

#Formula in Worksheet: =RUNSCRIPT('script-1')

#python script-1:

A1=vs.CellValue(1,'A')

pic=vs.GetObject('pic-1')

if A1 >= 0:

pic=vs.GetObject('pic-2')

vs.WSScript_SetResImage(pic)

"Just" have to find cell number, the script runs. To read out relative cells to the script cell.

Link to comment
  • 2 months later...

Hi

Attached, an Example.

Dom

1. Script in the cell:

=RUNSCRIPT('kantenbild-script')

2. Script which will be executed:

sheet_h=vs.ActSSheet()

if sheet_h != None and vs.GetName(sheet_h)!='XGST-VSSM Liste mit KB':

numRows, numColumns=vs.GetWSRowColumnCount(sheet_h)
startrow=9
rows=numRows-startrow

for x in range(rows):
	kabi=[]
	k=[]
	k1=vs.GetWSCellString(sheet_h,x+startrow, 14)
	k2=vs.GetWSCellString(sheet_h,x+startrow, 16)
	k3=vs.GetWSCellString(sheet_h,x+startrow, 18)
	k4=vs.GetWSCellString(sheet_h,x+startrow, 20)
	k=([k1],[k2],[k3],[k4])
	for i in range(4):
		if k[i] !=['']:
			kabi.append('1')
		if k[i] ==['']:
			kabi.append('0')

	vs.SetWSCellVertAlignment(sheet_h, startrow, 24, numRows, 24, 3)
	vs.SetWSColumnWidth(sheet_h, 24, 24, 37)
	vs.SetWSImgSizeType(sheet_h, x+startrow, 24, x+startrow,24, 1)
	vs.SetWSImgSize(sheet_h, x+startrow, 24, x+startrow, 24, 48, 48)
	vs.SetWSRowHeight(sheet_h, startrow, numRows, 37, True, True)
	vs.SetWSCellFormula(sheet_h, x+startrow, 24, x+startrow, 24, '=IMAGE(N=\'n.vssm.k'+str(kabi[0])+str(kabi[1])+str(kabi[2])+str(kabi[3])+'\')')

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...