Home > IT Answers > Microsoft Windows > Can data on a second sheet be pulled up by a ...
LeviG
5 pts.
Q:
Can data on a second sheet be pulled up by a cell on sheet 1?
Excel 2007, formulas, multiple sheets
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
ASKED: Nov 26 2009  8:51 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Chatmaker
950 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
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.

Scenario 1-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): =IF(A3<>"", "555-1115","") . You could copy the formula to all rows where you might type in a doctor's name in column A.

Scenario 2-A little more complex but perhaps more meaningful
If you want the doctor's phone number to show up, use this: =IFERROR(VLOOKUP(A3,Doctors,2,0),"Doctor not found in table"). 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".


Scenario 3-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 Menu Insert and click Module
3) Make sure the code window appears by using menu View then click Code
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
Last Answered: Nov 26 2009  5:34 PM GMT by Chatmaker   950 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SbElectric   1765 pts.  |   Nov 28 2009  4:42AM GMT

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