I have been searching the forums here and have found a few things but nothing that I have been able to use to do what I want exactly.
I have a file with several worksheets. 27 to be exact. A main sheet and then 26 others that are named A - Z.
Each A - Z sheet has 2 columns: A and B with a varying numbers of rows.
On the main sheet I have two fields that I each want to display the data from a random cell on one of the other sheets. The fields have to correspond so if the first field displays the data from A6 on sheet "F", the second field should display the data from B6 on sheet "F".
I don't know if this is possible but I feel that it is. One thing I considered is merging the A - Z sheets into one worksheet if that would be easier but if it can be done with the way I have it now I'd rather do it that way.
Last edited by Hungrymoose; 12-15-2010 at 09:57 AM.
I think I almost got it. I decided that it would be easier to merge all the A - Z sheets into one. I have a function on that page that counts the number of rows with values and returns that number. I also have a function on that page that generates a random number between 1 and that value. What I can't seem to get now is how to display the data from a cell matching that number.
For instance; if the random value is 125 then I want to display the value from row A125. Lets say my random value number is displayed in cell E5; The problem is how to get a formula like =A(E5) so basically it will return the random number from E5 and pass it on to the =A to display the value from A125.
Does that make sense? Help please!
=address(integer(random()*200),1)
OR
=INDIRECT(address(integer(random()*200),1))
Thanks, you got me on the right track! I got by using: =INDIRECT("A" & E5)
I then combined my random number and cell count functions: =RANDBETWEEN(1;COUNTA(A1:A2996))
Everything is working great now. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks