0

I have a list of 148 names in Column A. I need a function that would let a user enter in a number and use that number to return a list of names in column B or C. There can be repeats. So column B would prompt me with: "enter the number of names needed", and after I entered in the number, such as "4", below that prompt or in column C "4" names would be listed in different cells or even the same cell separated by commas. This workbook will eventually be embedded in a Wordpress.com site.

I looked online for some examples. This blog explains something similar:
http://datawiz.wordpress.com/2011/01...-from-a-sheet/

They prompt a user to look up the popularity of baby names. But since I don't know how to do very much in Excel, I can't figure out how to exactly apply his formulas to my problem.

Any suggestions?

I should also say that I already tried these formulas, which only give me one result in the cell that contains the formula:

=INDEX($A1:$A148,RANDBETWEEN(1,COUNTA($A1:$A148)),1)

=INDEX(A1:A148, RANDBETWEEN(1, 148))

=INDEX(Tanglelist, RANDBETWEEN(1, 148))

I've asked this on other forums with no solution. But someone suggested this formula which I guess is a step in the right direction:

=IF(ROWS(B$2:B2)>$B$1,"",INDEX(Tanglelist,RANDBETWEEN(1,ROWS(Tanglelist))))
  • The Wordpress link no longer exists. – fixer1234 Oct 24 '14 at 18:30
  • Can you update the link? – RockPaperLizard Oct 24 '14 at 21:28
2

Just take your result one step farther. Say the number is entered in B1. In C1:

    =IF(ROW(c1)>$B$1,"",INDEX($A$1:$A$148,RANDBETWEEN(1,148)))

Copy this down the column. Now you will get a random selection for the number of rows in C specified in B1.

  • I don't want to copy and paste down all the cells - that is pointless. I might as well just refresh the one cell with the formula over and over again with F9. I need a formula that will return a list. Otherwise why would it even be necessary for the user to enter in a number of names needed. – Rita Oct 25 '14 at 3:42
  • This does return a list. You create the formula once, then copy it into the entire range once as one copy/paste step. Then all of the cells sit there waiting for you to enter a number in B1 for how many you want. That quantity of names then appears in the list. If you put a different number if B1, the list will display that number of names. If the number is less than previous, the extra names from the previous quantity will be blank. – fixer1234 Oct 25 '14 at 3:48
  • Every time a number is entered in B1, you will get a fresh random list of names. Each cell that contains the formula will independently select a random name every time Excel recalculates. What I added to the beginning of your formula checks to see if the row that the formula is in is less than the count of names you want. The first N rows of your formula will display a random name in each cell and the rows after that will be blank. – fixer1234 Oct 25 '14 at 4:03
  • If I'm missing what it is that you want to accomplish, try what I suggested, which is a quick exercise. Then let me know what is different from what you are looking for. That would help me to understand what you need. – fixer1234 Oct 25 '14 at 4:07
  • It just occurred to me that you might have been visualizing something like a VBA approach. Putting a new number in B1 would trigger a program that would clear out the old list and build a new one by running a formula to generate a random name for the number of times entered, and putting the results in sequential cells. That would produce the same result but it would be more complicated to do it that way. – fixer1234 Oct 25 '14 at 4:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.