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