Can data on a second sheet be pulled up by a cell on sheet 1?

5 pts.
Tags:
Excel 2007
formulas
Hi... I work in an Emergency Room, and I'm trying to set up either a formula or macro so that when I'm making the "Call List" for the day I can type out the doctor's name in the "A" column, and in the "C" column of the same row have the phone number for the office automatically populate into that cell. Is there a way to do this without buying an expensive add-in? Is there a way I can program this? I've been looking, google'ing everything I can think of to try to get this to happen. I'm working with a second sheet within the workbook so that all the names and phone numbers are hidden in the back end of the spreadsheet. So far I have on the second sheet, where the phone number for each doctor would be listed: =IF(General_on_call="doctor'sname", REPLACE(Sheet1!C3,1,8,"555-1115"),"Not Oncall") but this does not populate the phone number into Sheet 1 cell C3 where I want it. Is it possible to do this, and if so, how? I'm about to scrap this whole project and have an officespace style beat down on the computer. Any help would be appreciated. Thank you!



Software/Hardware used:
Windows XP, Office 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

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″
Else
OnCall = “Not On Call”
End If

End Function

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SbElectric
    I agree with Chatmaker’s suggestions and recommend to use Scenario 2. My comments are just to elaborate and clarify Chatmaker’s solution. Use Sheet 2 as Master record – meaning you may enter all needed information for the doctors. Row 1 may have headings like: Name, Title, Phone number, Schedule, Remarks etc. Row 2 onwards will have the real data. In that case your actual data table for 50 doctors will be A2 thru E51. Sheet1 will be the Emergency Room Call List. Row 1 will have Headings like Name (col A), Telephone (col C), Remarks (Col D), if needed. Now on Row 2 Type the name on A; then on Row 2 Col C: Type =IFERROR(VLOOKUP( A2,Sheet2!A2:E51,3,0), “Name not on table”) If you want to populate the Remarks on col D, then on Row2 col D type: =IFERROR(VLOOKUP(A2,Sheet2!A2:E51,4,0), “Not in table”) Hopefully, you will see that telephone number and remarks are copied from Sheet2 master record. Now for Row 3 col C and D – type the same except change only the first A2 to A3 (rest should be the same). If any problem, just look up on VLOOKUP – it is pretty simple. Good luck & let us know if it worked. SbElectric
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following