# Thread: Phone Tree

1. ## Re: Phone Tree

Wrong forum, John?

2. ## Phone Tree

Is there a phone tree form written in Excel or Word available? The tree would reflect one person contacting two others who each in-turn contact another two and etc..

I started to design one in Excel but decided to ask prior to going any further.

Thanks,
John

3. ## Re: Phone Tree

John

Is there a maximum number of people you require on the phone list? In my experience it is about 30, being the normal size of a school class or scout group as I am just playing with an idea or two.

4. ## Re: Phone Tree

Sorry, now have a cunning theory that has just panned out from the previous post.

The mathematics behind a contact list is a simple mathematic progression. Assuming x is the position number in the list the people that x has to call is 2x and 2x+1

There will be a point in the list that calls will not be required as they have been covered by the people higher up in the pyramid.

Have a look at the attached workbook. Sheet 2 shows my pyramid theory and sheet 1 the solution...A bit course but could give you some ideas

5. ## Re: Phone Tree

Stuart

As I said in the reply, it is a coarse outline The simple addition of a new column listing the individuals telehone numbers will easily rectify the issue.

I was simply trying to show that there is a mathematical formula to assist the retrieval of the numbers and the indirect function can do the retrieval. John appears to in the process of creating a phone tree so I wasn't going to build a full application

6. ## Re: Phone Tree

Jezza,

I don't think this can work, as I don't know Mable's phone number <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

StuartR
Edited by StuartR to add a smilie. I forgot that people can't always read my mind, and I guess I shouldn't be making flippant jokes in a serious thread anyway <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

7. ## Re: Phone Tree

Jerry,

Thanks for the suggestion. This is what I came up with.

Regards,
John

8. ## Re: Phone Tree

Cool, very clever, so concurs with my little algorithm <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

9. ## Re: Phone Tree

John

I can see you beavering away in the Excel forum at the moment creating macro buttons and tool tips....Just a suggestion , I hope you don't mind but why don't you completely automate it using the worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPosition
Dim oName

Sheets("Sheet1").Select
Range("A2").Select

Do Until IsEmpty(ActiveCell)
oPosition = ActiveCell
oName = ActiveCell.Offset(0, 1)

Sheets("Tree").Shapes("Rectangle " & oPosition).TextFrame.Characters.Text = oName
ActiveCell.Offset(1, 0).Select
Loop
End Sub

All your code just place the code in sheet 1 of the VBE and it changes your tree on the tree tab immediately every time you change the persons name or the order of the people. Just a thought, I like the KISS principle <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

10. ## Re: Phone Tree

Jerry,

Good point. Quite honestly, I didn't even think of it.

Thanks,
John

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•