# How can I extract data from the following worksheet?

#### VishalK

##### New Member
finished.xls
ABCDEFGHIJKLMNOPQRSTUVW
1VKNKHYGPCJETSSundries
20FloatCashCCChequeCashCCChequeCashCCChequeDentistCashCCChequeCashCCChequeCashCCCheque
3
4
5Cash to Bank
6
7Smith, A50.00
8Jones, B25.00
9Doe, J35.00NK
10Evans, J29.00
Monday

I have included an excerpt from my spreadsheet above. There is a similar worksheet for each day of the week within the workbook, and I have been trying to extract 2 sets of data, which I suspect are possible with pivot tables, but each time I try, I either get an error, or get horribly lost.
Note the coloured cells in row 3 are sets of 3 merged cells.

The data I wish to extract are:

1) I would like to have a separate worksheet in the workbook into which I are extracted from each of the weekday sheets all payments by cheque, and from whom (i.e. a list of names and amounts paid by cheque is generated.)

2) A bit of explanation for this one: A patient seeing the hygienist is charged a standard fee of £35 for a 30min appointment. This is entered under the HYG section (Columns I,J,K), along with the originating dentist in column N (from a drop down list). This is because 10% of the hygiene fee is given to the referring dentist. Each entry in column N is added up against each dentist name. A standard calculation is done multiplying this number by 3.5 to give the 10% referral fee in an ongoing calculation. However, some patients come in for extended appointmements, for which a greater amount is charged. Unfortunately the calculation as it stands does not distinguish that the patient has paid more than £35 and therefore the 10% referral fee is incorrectly calculated. My idea is then to have a separate worksheet in which the name of only those patients who have paid more than £35 in columns I,J or K, together with the originating dentist in column N for that payment are liste. Thus I can manually correct the hygiene referral fees. Is this possible, or can anyone suggest a more elegant way of correctly calculating the hygiene fees from the outset?

Sorry for the long post, but thanks for any and all help. This forum has been a revelation so far, and I am eternally grateful to those who have given so kindly of their time and knowledge so far.

Kind regards

Vishal...

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Replies
9
Views
157
Replies
5
Views
507
Replies
2
Views
238
Replies
9
Views
314
Replies
5
Views
613

1,203,460
Messages
6,055,556
Members
444,796
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back