Glyn Merritt
asked on
Referral Database with multiple practices and multiple referrers
Referral Database with multiple practices and multiple referrers
I’m creating a referral database and the best tool we have at our available to all at our disposal is Excel 2007 unfortunately Access is not on all required PC’s.
I need help setting up the sheet so we can fill in the list of GDP’s note the practices they work at and where they refer to.
We have 9 practices and one GDP (Individual Dentist) can refer to up to 3 of our practices
Sheet Descriptions
GDP – One list of unique Dentists (individuals) of who will work at up to 3 Referral Practices each picked from drop down lists from the Referral Practices sheet)
Also
The Practice 1, 2 and 3 Fields would be selected form drop down list linked to OUR Practice’s
Referral Practices – The list of the dental Practice’s that the GDP’s work at (effectively Contact details for each dental practice)
Our Practices – The Codes used to identify our practices
SOP, BOP etc… It would be great if this sheet could automatically fill in from the GDP sheet with record that has the relevant Ref TO selected against one of their record (column highlighted in blue)
These sheets will be used to enter the information for the number of referrals made by each GDP to each of our practices for that year.
All columns coloured in yellow would need to be selected from drop down lists
Now the really tricky bit is that I would like to add in a number of referrals for each month in the SOP, BOP etc.. sheets to be totalled up in the GDP sheet so we can see who refers the most..
GDP---Ref.xls
I’m creating a referral database and the best tool we have at our available to all at our disposal is Excel 2007 unfortunately Access is not on all required PC’s.
I need help setting up the sheet so we can fill in the list of GDP’s note the practices they work at and where they refer to.
We have 9 practices and one GDP (Individual Dentist) can refer to up to 3 of our practices
Sheet Descriptions
GDP – One list of unique Dentists (individuals) of who will work at up to 3 Referral Practices each picked from drop down lists from the Referral Practices sheet)
Also
The Practice 1, 2 and 3 Fields would be selected form drop down list linked to OUR Practice’s
Referral Practices – The list of the dental Practice’s that the GDP’s work at (effectively Contact details for each dental practice)
Our Practices – The Codes used to identify our practices
SOP, BOP etc… It would be great if this sheet could automatically fill in from the GDP sheet with record that has the relevant Ref TO selected against one of their record (column highlighted in blue)
These sheets will be used to enter the information for the number of referrals made by each GDP to each of our practices for that year.
All columns coloured in yellow would need to be selected from drop down lists
Now the really tricky bit is that I would like to add in a number of referrals for each month in the SOP, BOP etc.. sheets to be totalled up in the GDP sheet so we can see who refers the most..
GDP---Ref.xls
Apologies, I posted the wrong picture. THIS is what the summary tab GDP looks like:
Cheers,
Dave
Cheers,Dave
ASKER
That is truly great stuff....
Is there a way to filter the viewed GDP in each sheet so it displays only the relevant one for each
i.e. In the HHOP sheet only the GDPs with HHOP selected in the Ref TO 1 2 or 3 are displayed in the HHOP sheet.
Is there a way to filter the viewed GDP in each sheet so it displays only the relevant one for each
i.e. In the HHOP sheet only the GDPs with HHOP selected in the Ref TO 1 2 or 3 are displayed in the HHOP sheet.
Yes - I'll be offline for a couple hours, but will revert with that...
Dave
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My apologies, I've been away. I will have a little play if that is ok. I need to rename and move some columns but it looks great at first glance.
Cheers
Glyn
Cheers
Glyn
ASKER
I’ve attached the new sheet called GDP Database 2011. Basicly the fields in blue will be entered by the user and the white parts will be populated from within the sheet.
Here are the notes of t
GDP Sheet – Can we populate the address from Practice 1 of the GDP
GDP Sheet- are we able to combine all the referrals that the practices receive each month (which they will enter into their specific sheet eg. BOP, HHOP etc) into columns AD to AO.
Practice sheet- Can we populate columns N to P using the data entered into columns I to L on the GDP sheet cross reference with columns F to G?
GDP-Database-2011.xlsx
Here are the notes of t
GDP Sheet – Can we populate the address from Practice 1 of the GDP
GDP Sheet- are we able to combine all the referrals that the practices receive each month (which they will enter into their specific sheet eg. BOP, HHOP etc) into columns AD to AO.
Practice sheet- Can we populate columns N to P using the data entered into columns I to L on the GDP sheet cross reference with columns F to G?
GDP-Database-2011.xlsx
ASKER
Rapid response and spot on target! Thank you.
merrittuk - if you create a realted question to this one - see the "ask a related question" in the comment box, below, and paste your above "enhancement" question, I can respond accordingly.
Dave
Dave
1. I downloaded your sample and created three named ranges, that are dynamic, for use in the drop down validation lists
OurPractices_Names =OFFSET('Our Practices'!$A$1,MATCH(TRUE
ReferralPractices_Names =OFFSET('Referral Practices'!$A$1,MATCH(TRUE
Practitioners_Names =OFFSET(GDP!$B$1,MATCH(TRU
These three names create a dynamic array range that matches whatever is in the list (for rows 2 to 1000) - you can lengthen these or shorten these, by going to the Names manager and changing the 1000 to a larger or smaller number.
2. I then created the drop down validation cells for the YELLOW GDP columns(Practitioners_Name
This handles the first part of your question. Note, I put these validation cells down to row 23, which is where you stopped your formula list in column A of the GDP tab.
3. I then populated each of the Practices with the Practitioner's Names (to create some dummy data) in the same order as I found in the GDP tab.
4. I then generated random numbers (1 to 50) in each of the monthly cells, for each of the Practitioners (to create some dummy data)
5. I then added a Total for the Year at the end of each of the Refer To Tabs
6. I then added a column in the GDP tab for each of the Refer To practices.
7. I then added a Vlookup for each of these practitioners, by practice. This formula:=IF(ISERROR(VLOOKU
does a Vlookup of the practitioner's name against the practice listed at the top of each column. The indirect function converts that column header and corresponding address to a real address that the Vlookup can use. If you have more than 22 rows, you'll need to update one of the cells - say, L2 in the GDP tab and update the $22 to a larger number. Then you can copy down and across for as many practitioners and refer to practices that you have.
Take a look and let me know if you have any questions.
Here's what the summary GDP tab looks like:
So, in summary, you have your drop down lists for practitioners, referal practices, and our practices. These update automatically, based on adding more or less data to the lists you have created. You also have a summary section that reports the total for each practitioner for the year, by refer to practice.
See attached file:
Enjoy!
Dave
GDP---Ref-r1.xls