| finished.xls | |||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
| 1 | VK | NK | HYG | PC | JETS | Sundries | |||||||||||||||||||
| 2 | 0 | Float | Cash | CC | Cheque | Cash | CC | Cheque | Cash | CC | Cheque | Dentist | Cash | CC | Cheque | Cash | CC | Cheque | Cash | CC | Cheque | ||||
| 3 | |||||||||||||||||||||||||
| 4 | |||||||||||||||||||||||||
| 5 | Cash to Bank | ||||||||||||||||||||||||
| 6 | |||||||||||||||||||||||||
| 7 | Smith, A | 50.00 | |||||||||||||||||||||||
| 8 | Jones, B | 25.00 | |||||||||||||||||||||||
| 9 | Doe, J | 35.00 | NK | ||||||||||||||||||||||
| 10 | Evans, J | 29.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...
