MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula help.


Posted by Stuart on September 06, 2001 7:02 PM

Is it possible to have an argument that if the answer is true it will auto open another worksheet within the book?


Posted by Damon Ostrander on September 07, 2001 9:27 PM

Hi Stuart,

This cannot be done with a formula or custom VBA function, because these are both limited to modifying only the cell they are in. It would be quite a problem for traceability in Excel if worksheet functions could modify other areas of a workbook.

That all being said, what you most likely want can be accomplished in another way. If you want to be able to have a worksheet activated upon the value of a cell becoming TRUE, this is easily done with the worksheet Calculate event. For example, say you want to activate worksheet "Data" when cell B4 on current sheet is activated. Simply put the following code into the sheet's calculate event:

If Not Intersect(Target,[B4]) Is Nothing Then
If Target.Value = True Then
Worksheets("Data").Activate
End If
End If

The difference here is that it is not a formula or function in the cell that is activating the worksheet, but rather a macro triggered by an event.

Happy computing.

Damon