Workbook_Change help please!

[Gingertrees]

OK I tried but I can't figure this one out. I have a list of IDs on Sheet1, cells A3:A7, then a list of dates associated with each of these IDs, cells C3:C7. At the top of Sheet 1, in cell B1, is a dropdown list of these IDs. I choose one, and that selection is populated into Sheet 2.
=========
Sheet 1
=========
col A.............|B.................|C......
------------------------------------
1 | Choice......|*RG6*\/.......|..............
2 | ID............|STDATE.......|ENDDATE
3 | AD2..........|4/5/2010.....|10/4/2010
4 | RG6..........|5/26/2010....|11/24/2010
5 | PD8..........|9/13/2010....|3/14/2011

Sheet2: Sheet 1, cell B1 = Sheet 2, cell F1
Sheet2, cell B3 = ENDDATE (via Vlookup from Choice in sheet2, cell F1)
Sheet2, cell B2 = roughly the number of months between today (D1) and ENDDATE (B3). Like so:

=========
Sheet 2
=========
col A.............B...............C..............D.............E.........F.......
-----------------------------------------------------------
1 |...............|................|Today:.....|9/24/2010|..........|*RG6*
2 |Months: ...|_________..|..............|..............|..........|.........
3 |EndDate:...|11/24/2010.|..............|..............|..........|.........
4 |...............|................|..............|..............|...........|........
5 |...............|................|..............|..............|...........|.........
Now comes the calculation/workbook change that I can't figure out.
If the ID changes, on sheet 1, I need both the EndDate and number of months on Sheet 2 to update. I put together a Cases statement for changes in B3 for that:
Case Is <= MeDate + 20
[B2] = "N/A1" 'good for number of months
Case Is <= Today+ 32
[B2] = "1"
Case Is <= Today+ 63
[B2] = "2"
Case Is <= Today+ 92
[B2] = "3"
Case Is <= Today+ 123
[B2] = "4"
Case Is <= Today+ 165
[B2] = "5"
Case Is >= Today+ 166
[B2] = "6"
Case Else

I managed this when I had just some test data all on one sheet, no references etc with a Worksheet_Change (that's how I put together the Cases thing). And yes there's a reason I didn't just use the DateDiff function, but not important.

***So in sum: If Sheet1("B1") changes, that change needs to affect Sheet2("F1") and Sheet2("B2"). Your help is appreciated!***

 

[tweedle]

I believe this is what you seek ...
For (Num of Month) Sheet2!B2

Insert a VBA Module, or add to an existing:

[/SIZE]
[SIZE=1]Public Function funRetNumMonths(dateIN As Range) As String[/SIZE]
[SIZE=1]'This could admitidly be more verbose in error-handling. [/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]Dim ENDDate, Today As Date
Dim retval As String[/SIZE]
[SIZE=1]If IsNull(dateIN) Or dateIN = "" Then Exit Function[/SIZE]
[SIZE=1]ENDDate = dateIN
Today = Format(Now(), "mm/dd/yyyy")[/SIZE]
[SIZE=1]If IsDate(ENDDate) Then
    Select Case ENDDate
        Case Is <= Today + 20
        retval = "N/A1" 'good for number of months
    Case Is <= Today + 32
        retval = "1"
    Case Is <= Today + 63
        retval = "2"
    Case Is <= Today + 92
        retval = "3"
    Case Is <= Today + 123
        retval = "4"
    Case Is <= Today + 165
        retval = "5"
    Case Is >= Today + 166
        retval = "6"
    Case Else
    
    End Select[/SIZE]
[SIZE=1]Else
    Exit Function
End If[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]funRetNumMonths = retval[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]End Function

[/SIZE][SIZE=1]

Then (Num of Month) Sheet2!B2: =funRetNumMonths(B3)

 

[shg]

How about just a formula?

=LOOKUP(B3-TODAY()-1, {-40000,20,32,63,92,123,165,40000},{"N/A1",1,2,3,4,5,6})

 

[xenou]

I wasn't sure reading your post if it was the logic of the code or getting the code to fire properly ... I sort of like the formula approach too, though, which may solve both.

 

[shg]

The last value in the first array isn't necessary, sorry:

=LOOKUP(B3-TODAY()-1, {-40000,20,32,63,92,123,165},{"N/A1",1,2,3,4,5,6})