Desperate--need by tomorrow-please help!!
Posted by chad on January 18, 2002 4:59 PM
I have two problems that I haven't been able to resolve in several hours. First, I am in one sheet in a workbook and I need to get some data from another sheet. The data is a date(numeral) which I am going to add two to then use as the row for a column in that same worksheet. See the data I'm using:
=IF(Summary!M((Summary!B19)+2)=5000, 1,2)
Anybody have an idea what's the prob?
Also, I have never used the Visual Basic Editor but I can program in Java & a little in C... I want to use the editor to write some code, how do I write it there?
Also, I need a snipet of code that, when some data is entered into a particular cell - then excel will automatically jump you to another worksheet in the same workbook, and also in one in another workbook if possible.?.?
I am at the end of me, can anyone help?
THANKS!!
~chad~
Posted by Tom Dickinson on January 18, 2002 8:11 PM
I put the answers with the questions.
Try
=IF(OFFSET(Summary!M1,Summary!B19+1,0)=5000, 1,2)
I have only added "1" instead of "2" because this is an offset, not a lookup.
Click on Tools, Macro, Record on your tool bar.
This requires 3 macros. In the macro sheet, put the selection commands, such as:
Sub ChangeTheChannel()
Sheets("Summary").Activate
Range("Summary!A1").Select
End Sub
Then in the object sheet for the spreadsheet (or whatever the name is, it is listed in the same place as the macro sheets)
put something like this:
Dim Strng As String
Private Sub Worksheet_Activate()
Strng = Range("A5")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A5") <> Strng Then
Call ChangeTheChannel
End If
End Sub
...and that should do it.
