Want to retrieve/display client visit dates by client name

I want to be able to type a client's first and last name in a cell in one sheet, and have it pull/retrieve from another sheet all dates the client has visited my clinic. This other sheet is an exported file from an online scheduler I use. It is less than elegant and cannot be customized.

I've provided a sample workbook for an idea as to what I am trying to achieve. Invoice sheet requires client first and last name (E8). B10 service date field needs to be populated with all the dates he/she has visited. The rest of the information can be static, but it would be nice if it automatically populated too.

I came up with this formula but it's not working:

=IF(ISERROR(INDEX($C$2:$N$400,SMALL(IF($C$2:$C$400=$Q$4,ROW($C$2:$C$400)),ROW(1:1)),2)),"",INDEX($C$2:$N$400,SMALL(IF($C$2:$C$400=$Q$3,ROW($C$2:$C$400)),ROW(1:1)),2))

From the attached images below, I want to be able to type "Jill Green" in the Claim#/Client Name box, and have all the dates she's visited display under SERV.DATE below.

Thank you.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

You lost your workbook.

63fal,

ideally I'd like little to no formatting on the data as its spit-out by Acuity Scheduler. I want other people to be able to use this, most of which have little to no excel experience.

Rate and GST are static, would there be a way to have some sort of IF statement where if a date is returned it auto fills those to a static number?

Duration of the session is not important at all and I'd rather it not be included.

Thank you for your assistance.


UPDATE: B11 works like a charm! Thank you
A11 does not fill beyond the first cell. I copied the formula throughout that column but still won't display data beyond the B11 cell.

Upon even FURTHER review, I now realize that RATE comes from the "Appointment Price" column, GST is a 5% sales tax, and Total is the sum of RATE and GST.

So, here are all of the formulas:

A2 =IFERROR(INDEX(TEXT('Invoice sample.csv'!B$2:B$18-'Invoice sample.csv'!A$2:A$18,"[m]")&" Min "&'Invoice sample.csv'!G$2:G$100,SMALL(IF('Invoice sample.csv'!C$2:C$18&" "&'Invoice sample.csv'!D$2:D$18=E$8,ROW('Invoice sample.csv'!A$2:A$18)-(ROW('Invoice sample.csv'!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

B2 =IFERROR(INDEX('Invoice sample.csv'!N$2:N$100,SMALL(IF('Invoice sample.csv'!C$2:C$18&" "&'Invoice sample.csv'!D$2:D$18=E$8,ROW('Invoice sample.csv'!A$2:A$18)-(ROW('Invoice sample.csv'!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

C2 =IFERROR(INDEX('Invoice sample.csv'!I$2:I$100,SMALL(IF('Invoice sample.csv'!C$2:C$18&" "&'Invoice sample.csv'!D$2:D$18=E$8,ROW('Invoice sample.csv'!A$2:A$18)-(ROW('Invoice sample.csv'!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

D2 =IF(C11="","",0.05*C11)

E2 =IF(C11&D11="","",SUM(C11:D11))

Drag all formulas through row 22.

See attachment.

Also, you have to save your workbook as a .xlsx (Excel 2007 or newer) in order for these formulas to work.
.xls does not support IFERROR.

Edit: I just read your post #9. If you do not want to include the duration in column A, use the formula for A11 in post #7.

Glad we could help.

Thanks for the rep!

One last question:

I am creating another sheet called Claim Number. Here I have Column A (Client name) and Column B (Claim #). I want to be able to query in the invoice and having the client's claim # show up.

I'd like for this to show up in E7 above the client's name of the invoice image posted above.

Can I just modify the formula you gave me to do this?


I tried B2 =IFERROR(INDEX(‘Claim’!B$2:B$100,SMALL(IF(‘Claim’!A$2:A$18&" “=E$7,ROW(‘Claim'!A$2:A$18)-(ROW(‘Claim'!A$2)-1)),ROWS(A$1:A1))),"") however it doesn't work.

Here's the workbook with the new sheet.