|5||Cash to Bank|
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.