Referencing data from Sheet to Sheet


New Member
Apr 21, 2006
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)

Forum statistics

Latest member

Some videos you may like

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...