I have a spreadsheet (I can upload it here, but I don't know how to do it...) where Sheet1 A1:A4 are drop down lists that allow users to select various shapes ("", Square, Circle, Triangle, Rectangle). On Sheet2 I have the same 5 options listed from A1:A5. I also have images of these shapes in B1:D5 where the color of the images in column B is black, C is red, and D is green. The cells with the "images" of the "" option are all blank. I created dynamic images in Sheet1 B1:B4 that are all named Shape1 - Shape4 by row. I also have a function in Sheet1 C1 that is randbetween(2,4).
The equation in the name manager for Shape1 is: =INDEX(Sheet2!$A$1:$D$5,MATCH(Sheet1!$A$1,Sheet2!$A$1:$A$5,0),Sheet1!$C$1)
Shapes 2-4 are all the same except for the first part of the match function.
My Question: I want to delete the randbetween in the Sheet1 C1 and have a macro that will generate a random number between 2 & 4, and then subsequently update all of the dynamic images. How do I do this?
The reason for this is speed. I have another spreadsheet that is just a more elaborate version of this with many more dynamic images, and a much larger matrix of images in Sheet2. I have a feeling that I can speed up the generation of the random number and updating of all of the images if I use VBA (as of now if I put a random value in C1 it will update the dynamic images in a couple of seconds). I'm not positive but I think that using VBA excel can look at the entire matrix of images once, create the nonvolatile random number, and update all of the dynamic images pretty quickly. I understand that some functions are faster/slower than others (why I use the index/match here instead of indirect or maybe vlookup would work), but don't really understand why or how VBA can help with that.
I'm not an expert at VBA, but I have a basic understanding of what is going on. I'm looking for any suggestions on how to update the dynamic images or any broader suggestions that might speed up a spreadsheet like this one. Any ideas are appreciated. Thanks!
The equation in the name manager for Shape1 is: =INDEX(Sheet2!$A$1:$D$5,MATCH(Sheet1!$A$1,Sheet2!$A$1:$A$5,0),Sheet1!$C$1)
Shapes 2-4 are all the same except for the first part of the match function.
My Question: I want to delete the randbetween in the Sheet1 C1 and have a macro that will generate a random number between 2 & 4, and then subsequently update all of the dynamic images. How do I do this?
The reason for this is speed. I have another spreadsheet that is just a more elaborate version of this with many more dynamic images, and a much larger matrix of images in Sheet2. I have a feeling that I can speed up the generation of the random number and updating of all of the images if I use VBA (as of now if I put a random value in C1 it will update the dynamic images in a couple of seconds). I'm not positive but I think that using VBA excel can look at the entire matrix of images once, create the nonvolatile random number, and update all of the dynamic images pretty quickly. I understand that some functions are faster/slower than others (why I use the index/match here instead of indirect or maybe vlookup would work), but don't really understand why or how VBA can help with that.
I'm not an expert at VBA, but I have a basic understanding of what is going on. I'm looking for any suggestions on how to update the dynamic images or any broader suggestions that might speed up a spreadsheet like this one. Any ideas are appreciated. Thanks!
