Button linking to another cell and sheet

**SoutherBoy718** · 01-06-2007, 08:21 PM

What I'm trying to build here is an automatic drawing simulation instead of giving out tickets and drawing them from a hat or drum.

Setup: I have 2 worksheets where Sheet1 contains a command button and will display results starting on row 15 (A15) while incrementing by 1 (A16) each time the command button is hit. The data which should be shown on these rows (A15, A16, etc) will be derived from Sheet2 which has one column of names (A1 thru 'end of names').

So far, I've been able to "randomize" the names on Sheet2 to receive 1 result (or pick) by using this formula and hitting F9: =INDEX(A:A,RAND()*COUNTA(A:A)+1)

My Questions:
1 - How can make the command button on Sheet1 perform the function of hitting F9 on Sheet2 and receive the result starting on row 15 - Sheet1?

2 - Instead of just receiving data/value from 1 column, is it possible to receive a row of data/values from let say, 4 columns (Sheet2 - A25 thru D25), and have this be populated starting on row15 - Sheet1?

Is this possible?

Please let me know if more details are needed. Many "thanks" in advance for your time and effort on this.

**MSP77079** · 01-06-2007, 09:22 PM

How can make the command button on Sheet1 perform the function of hitting F9 on Sheet2 and receive the result starting on row 15 - Sheet1?

Please Login or Register  to view this content.

Note this code is using the CodeName for Sheet2, which might not agree with the "tab" name for sheet2.

is it possible to receive a row of data/values from let say, 4 columns (Sheet2 - A25 thru D25), and have this be populated starting on row15 - Sheet1?

Please Login or Register  to view this content.

**SoutherBoy718** · 01-06-2007, 10:05 PM

Thanks you very much for the reply.

I do have another question in which I didn't ask before....

In the situation above, how would you randomize 4 columns of data (or multiple rows) from Sheet2 when hitting the command button on Sheet1? And, ofcourse returning the value to row15....then on the next hit, row16, and so on?

Here's an example of 2 rows of data from Sheet2....

........ A ......... B ........ C ............. D ........
1..... John ..... Doe ... AnyCity .... AnyState
2..... Jane ..... Due .. SomeCity .. SomeState

Sorry for not asking this the first round and thanks for your help.

**MSP77079** · 01-06-2007, 10:26 PM

This is how I would do it. Just for demonstration, I put the data in a different column than I took it from. And, I delete the columns I write to, so be careful. Save a back up copy before testing (always a good idea).

Please Login or Register  to view this content.

**MSP77079** · 01-07-2007, 12:44 AM

Here is code to do both. It does not randomize the data, it merely pull a random row each time ...

Please Login or Register  to view this content.

**SoutherBoy718** · 01-07-2007, 11:11 AM

Thank you very much for all your help and time with this. Everything is working great.

Button linking to another cell and sheet

LinkBack

Thread Tools

Rate This Thread

Display

[RESOLVED] Button linking to another cell and sheet

Another approach

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions