is it possible to return multiples of an image based on the quantity as stated in another cell?

[torch1ight]

Noob ish user.

I have apples in a1 and oranges in b1

I would like to show the quantities of each in c1 as an image

[TABLE="width: 500"]
<tbody>[TR]
[TD]apples[/TD]
[TD]oranges[/TD]
[TD]image[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]two apples and two oranges[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]three apples and 1 orange[/TD]
[/TR]
</tbody>[/TABLE]



is there a way of making excel return the images of both items. I have the images that I need (one apple and one orange) and have them on sheet 2. I know that this is easy if I just wanted it to return a number, I cant see where I can make it work out how many pictures of apples I need.

thanks in advance for any help

 

[Eric W]

There are ways to change a picture based on a cell value. See https://exceloffthegrid.com/automatically-change-picture/

However, I couldn't figure out a way to repeat the pictures based on a count. You could write an event handler macro to monitor those cells, then copy as many images as you want, but that seems overly complicated.

If you are willing to use a different picture, you can use a UNICODE image. The UNICODE value for an orange is 127818 (1F34A in hex), and the UNICODE value for an apple is 127823 (1F34F in hex). These values I found from here:

http://unicode.org/charts/

under Miscellaneous Symbols and Pictographs.

Then you could set something up like this:

	A	B	C	D	E	F	G	H
Apple	Orange	Banana	Cherry	Pictograph	Apple
????	Orange
????	Banana
??????????	Cherry

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]127823[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]127818[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]127820[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]127826[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=REPT(UNICHAR($H$1),A2)&REPT(UNICHAR($H$2),B2)&REPT(UNICHAR($H$3),C2)&REPT(UNICHAR($H$4),D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Sorry, the formatting tool doesn't seem to handle UNICODE characters. Imagine each ? in the picture is a tiny picture of the fruit.

Hope this helps.

 

[torch1ight]

There are ways to change a picture based on a cell value. See https://exceloffthegrid.com/automatically-change-picture/

However, I couldn't figure out a way to repeat the pictures based on a count. You could write an event handler macro to monitor those cells, then copy as many images as you want, but that seems overly complicated.

If you are willing to use a different picture, you can use a UNICODE image. The UNICODE value for an orange is 127818 (1F34A in hex), and the UNICODE value for an apple is 127823 (1F34F in hex). These values I found from here:

http://unicode.org/charts/

under Miscellaneous Symbols and Pictographs.

Then you could set something up like this:

	A	B	C	D	E	F	G	H
Apple	Orange	Banana	Cherry	Pictograph	Apple
????	Orange
????	Banana
??????????	Cherry

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]127823[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]127818[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]127820[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]127826[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=REPT(UNICHAR($H$1),A2)&REPT(UNICHAR($H$2),B2)&REPT(UNICHAR($H$3),C2)&REPT(UNICHAR($H$4),D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Sorry, the formatting tool doesn't seem to handle UNICODE characters. Imagine each ? in the picture is a tiny picture of the fruit.

Hope this helps.

Thanks for getting back to me Eric,

I kinda lied in my example for simplicity sakes, the actual items that I am counting are in no particular order, a green triangle, a red triangle, a green square, and a red square, will this make the solution easier.

Again thank you for your help in advance.

 

[Eric W]

Yeah, I kinda wondered about that. If you had a particular picture you wanted, and not a generic picture to be found in the UNICODE, then the previous idea wouldn't work. Triangles and squares are to be found though, and they also can be found in some fonts, like Wingdings, which would make it easier. But the colors will be an issue.

First, your shapes. Are they solid or outlines? Is the triangle just a standard equilateral triangle? Next, how are your quantities populated? Manually? Formula? Imported data?

 

[torch1ight]

Yeah, I kinda wondered about that. If you had a particular picture you wanted, and not a generic picture to be found in the UNICODE, then the previous idea wouldn't work. Triangles and squares are to be found though, and they also can be found in some fonts, like Wingdings, which would make it easier. But the colors will be an issue.

First, your shapes. Are they solid or outlines? Is the triangle just a standard equilateral triangle? Next, how are your quantities populated? Manually? Formula? Imported data?

Again thanks for your input Eric,

The squares and triangles and exactly that
-- corrupted image removed --
squares and triangles, i also have them in red! The forum didnt like me pasting in the images!?!?!?

Quantities are manually inputted, the reason that I need the actual images to show in the cell, is that I am cross referencing excel with another program. The second program looks for the info in a row and then exports that. I will manually cut and paste them and see how that works out.

I suppose I could just cut and paste each one, I thought that there might have been a way to utilize a 'function' in excel to do this. I see that if I wanted a single picture of either to appear, thats pretty straightforward, but to have multiples or both doesnt seem as though there is a way.

Again thanks for being interested enough to reply.

 

[Eric W]

This forum doesn't allow you to directly attach images or workbooks, but you can upload them to a file sharing service and post a link.

There are still a few options, but nothing that sounds like it's exactly like you want. For example:

	A	B	C	D	E	F	G	H
Red triangle	Green triangle	Red square	Green square
pp	pp	nnn	n
ppp	p	nn	nn
p	pp	nnn	nnnn

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=REPT("p",A2)[/TD]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=REPT("p",B2)[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=REPT("n",C2)[/TD]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=REPT("n",D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The image doesn't look exactly like my test sheet. Given this layout, select columns E:F and select Wingdings 3 as the font. Select G:H and select Wingdings as the font. Select E and G and set the font color to red, select F and H and set the font color to green. Now you have repeated squares and triangles.


If you actually want the images, is there an upper limit to the number of items? If there is, say 5, then you could make 5 images of red triangles, the first with 1, the second with 2, etc. 5 images of green triangles, and so on. Then make a table of the images. Then we could use the technique in the first link I posted to put a formula in E2:H2 to grab the appropriate image.


Final idea involves VBA. We could put a Worksheet_Change event to monitor your A2:D4 cells. If any of them change, a macro could paste the appropriate numbers of images. Kind of tricky though.


A few ideas to think about, or you could stick with your manual method. Depends on how many you have to do.

 

[David Luiz]

Great replies, a good way of explaining how to do it accurately. I use excel online. Time-consuming actually. I use MacBook Pro and it was facing a freeze a lot of times. Though Apple Support USA solved it, and now it works fine. Coming back to the thread, good replies.