Randomly display images from a different sheet?

[QuestionBaker]

Context: I am trying to make a question bank to generate different assignment for each student so as to minimize copying...

Task: I want to maintain a list of images in a sheet, let us call the sheet as LoI (for List of Images), and then I want randomly display a few of them on the Assignment sheet.

Question: is there a way to set it up in such a way that once I have the list, even if new images are added later, they could be displayed.

I've been able to do something similar text and not images. I achieved it using =RAND(), =INDEX() and =RANK.EQ()

 

[Yongle]

Re: How to randomly display images from a different sheet?

Try this in a NEW test workbook containing 2 sheets "Assignment" and "LoI"

In sheet LoI
- make each cell in range A1:A5 wide enough and tall enough to hold an image
- insert 5 images one in each cell A1:A5
- click on Formulas Tab \ Define Name \ enter Name as: myPic \ enter RefersTo as: =INDEX(LoI!$A:$A,Assignment!$A$1)

In sheet Assignment
- place this formula in cell A1 =RANDBETWEEN(1,5)
- make cell A2 the same size as A1 in other sheet (ie big enough to hold your images)
- copy (any) one of the images from LoI and place in A2
- with the image still selected, click on formula bar, and enter formula: =MyPic and confirm

Now hit {F9} to change the random number in A1

Logic
The image is assigned to Named Range "myPic" whose formula returns (from iColumn A in "LoI") the image whose row number is determined by the random number in cell A1 in "Assignment"

If for any reason you have any problem with any of the above, the method is very similar to what is in this video https://www.youtube.com/watch?v=0xJ22YLLy9M

 

[QuestionBaker]

Re: How to randomly display images from a different sheet?

Thanks a lot! It works like a charm.

Try this in a NEW test workbook containing 2 sheets "Assignment" and "LoI"

In sheet LoI
- make each cell in range A1:A5 wide enough and tall enough to hold an image
- insert 5 images one in each cell A1:A5
- click on Formulas Tab \ Define Name \ enter Name as: myPic \ enter RefersTo as: =INDEX(LoI!$A:$A,Assignment!$A$1)

In sheet Assignment
- place this formula in cell A1 =RANDBETWEEN(1,5)
- make cell A2 the same size as A1 in other sheet (ie big enough to hold your images)
- copy (any) one of the images from LoI and place in A2
- with the image still selected, click on formula bar, and enter formula: =MyPic and confirm

Now hit {F9} to change the random number in A1

Logic
The image is assigned to Named Range "myPic" whose formula returns (from iColumn A in "LoI") the image whose row number is determined by the random number in cell A1 in "Assignment"

If for any reason you have any problem with any of the above, the method is very similar to what is in this video https://www.youtube.com/watch?v=0xJ22YLLy9M

 

[Yongle]

Re: How to randomly display images from a different sheet?

=RANDBETWEEN is a volatile function
A cell containing the function changes its value whenever anything else changes
I do not think you want the images changing repeatedly
To "fix" the random number you must use some vba and there are several ways you could do that

One way would be to write the number to the cell directly

Range("A1") = WorksheetFunction.RandBetween(1, 5)

 

[QuestionBaker]

Re: How to randomly display images from a different sheet?

You are right, it is a volatile function, and since my aim is to print the assignment and not actually give them the excel file, it works just okay for my assignment.

But there is case where I do not want to randomness to be volatile and occur only when desired. I will remember your above instructions then.

=RANDBETWEEN is a volatile function
A cell containing the function changes its value whenever anything else changes
I do not think you want the images changing repeatedly
To "fix" the random number you must use some vba and there are several ways you could do that

One way would be to write the number to the cell directly

Range("A1") = WorksheetFunction.RandBetween(1, 5)