Hopefully I understand what you want. But let’s look at a couple of scenarios and hope one meets your needs.
First – it’s easier to “pull” data than “push” it.
<b>Scenario 1</b>-Simplest “Pull” solution
If all you want is the number “555-1115″ to show up next to any doctor’s name you type in on Sheet 1, then this would do it (in Cell C3): <b>=IF(A3<>””, “555-1115″,””)</b> . You could copy the formula to all rows where you might type in a doctor’s name in column A.
<b>Scenario 2</b>-A little more complex but perhaps more meaningful
If you want the doctor’s phone number to show up, use this: <b>=IFERROR(VLOOKUP(A3,Doctors,2,0),”Doctor not found in table”)</b>. This assumes you have named the list of doctors on the second sheet as “Doctors” with the first column being the Doctor’s name and the second column having the doctor’s phone number. If it’s the third column, simply change the “2″ in the VLOOKUP to “3″.
<b>Scenario 3</b>-Pushing Data
This is what your question looked like to me, but I hesitated to go here because, well, it’s not easy for most people and it’s ugly. With that said: you can create a User Defined Function (UDF) in Excel.
1) Go to the Macro Editor by press ALT-F11.
2) Use the <b>Menu </b>Insert and click <b>Module</b>
3) Make sure the code window appears by using menu <b>View </b>then click <b>Code</b>
4) Copy this code and paste it into the code window
Public Function OnCall(sDoctor As String) As String
OnCall = “”
If sDoctor = Sheets(“Sheet1″).Range(“A3″) Then
Worksheets(“Sheet1″).Range(“C3″) = “555-1115″
OnCall = “Not On Call”
Now instead of this: =IF(General_on_call=”doctor’sname”, REPLACE(Sheet1!C3,1,8,”555-1115″),”Not Oncall”)
Type this: =IFERROR(oncall(A4),”On Call”)
I don’t recommend this approach because when you push data from one sheet to another with a UDF, the UDF abruptly ends when the other sheet receives focus – thus the “IFERROR” is required to handle that. I don’t know how to get around that with a UDF. Perhaps someone else can improve this answer.
Best wishes: Craig