# Referencing data from Sheet to Sheet

#### cfarley

##### New Member
I am working on a financial document which tracks patient visits on Sheet 1 and the \$ per visit on sheet 2. For example: Sheet 1 - A6:A13 contains patient initials and numbers; B6:B13 contains an "x" indicating Visit 1 was done, or is left empty indicating the visit has not yet taken place, C6:C13 represents Visit 2, D6:D13 Visit 3, E6:E13 Visit 4, and F6:F13 Visit 4. On Sheet 2 A6:13 contains the same patient initials & numbers, but instead of X's in columns B,C,D,E, & F, the cells need to reflect the \$ for each visit. Visit 1 is \$1287.60, Visit 2 = \$263.00, Visit 3 = \$1235.00, Visit 4 = \$1199.00, and Visit 5 = \$1235.00. How do I create a formula that references sheet 1 (If Sheet 1 B6 = x then Sheet 2 B6 = \$1287.60). Please let me know if more information is needed. I got a response with this formula before, but did not have any luck with it...

Assuming that A2:A100 contains the patient name, and B2:B100 contains the corresponding 'X', try...

=CHOOSE(SUMPRODUCT(--(A2:A100="Pt #1"),--(B2:B100="X")),2300,3900,4900)

1,081,693
Messages
5,360,668
Members
400,592
Latest member
katekoz

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...