Solved

Referral Database with multiple practices and multiple referrers

Posted on 2011-03-22
9
350 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Glyn Merritt
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35196410
Ok - I'm not sure I understood your question 100%, but let's give it a try.

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,INDEX('Our Practices'!$A$2:$A$1000<>"",0),0),0,COUNTA('Our Practices'!$A$2:$A$1000),1)
ReferralPractices_Names =OFFSET('Referral Practices'!$A$1,MATCH(TRUE,INDEX('Referral Practices'!$A$2:$A$1000<>"",0),0),0,COUNTA('Referral Practices'!$A$2:$A$1000),1)
Practitioners_Names =OFFSET(GDP!$B$1,MATCH(TRUE,INDEX(GDP!$B$2:$B$1000,0)<>"",0),0,COUNTA(GDP!$B$2:$B$1000),1)

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_Names), Yellow Practice Columns (ReferalPractices_Names) and Blue Refer To Columns (OurPractices_Names).

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(VLOOKUP($B3,INDIRECT(L$1&"!$A$2:$N$22"),COLUMN($N$1),0)),"",IF(VLOOKUP($B3,INDIRECT(L$1&"!$A$2:$N$22"),COLUMN($N$1),0)=0,"",VLOOKUP($B3,INDIRECT(L$1&"!$A$2:$N$22"),COLUMN($N$1),0)))
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:
 view of summary GDP tab
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35196438
Apologies, I posted the wrong picture.  THIS is what the summary tab GDP looks like:

correct picture - view of summary GDP tabCheers,

Dave
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 35200145
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.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35201024
Yes - I'll be offline for a couple hours, but will revert with that...

Dave
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35202687
Ok.  I had to write a couple functions (to be adaptable to spreadsheet changes you might make) that gets the n-th instance of a ReferTo from the GDP page, so we could see the practitioner name in rank order.

The functions are as follows:
Function getSheetName(rng As Range) As String
    getSheetName = rng.Parent.Name
End Function
Function getGDPReferTo(ReferTo As String, RangeToLook As Range, instGDP As Integer) As Long
'gets the n-th instance of Practitioner who has Referred to this practice
Dim mySheet As Worksheet
Dim myRow As Long, myCol As Long
Dim firstrow As Long, lastRow As Long, colFrom As Long, colTo As Long
Dim foundOnRow As Boolean, foundInst As Long

    Set mySheet = RangeToLook.Parent 'Sheets(ReferFrom)
    
    'get rectange for RangeToLook range
    firstrow = RangeToLook.Cells(1, 1).Row
    lastRow = RangeToLook.Cells(RangeToLook.Rows.Count, 1).Row
    
    colFrom = RangeToLook.Cells(1, 1).Column
    colTo = RangeToLook.Cells(1, RangeToLook.Columns.Count).Column
    
    'search each row in RangeLook from left to right column.  If found on a row, then increment counter only once, and if that matches the search
    'instance, then exit function
    foundInst = 0
    For myRow = RangeToLook.Cells(1, 1).Row To lastRow
        
        foundOnRow = False
        
        For myCol = colFrom To colTo

            If mySheet.Cells(myRow, myCol).Value = ReferTo And Not foundOnRow Then
                foundInst = foundInst + 1
                foundOnRow = True
                If foundInst = instGDP Then
                    getGDPReferTo = myRow
                    Exit Function
                End If
            Else
                'do nothing
            End If
            
        Next myCol
    
    Next myRow

    getGDPReferTo = 0
    
End Function

Open in new window


WIth those, we can then do the spreadsheet function getGDPReferTo(ReferTo as String, RangeToLook as Range,instGDP as long) as Long, which looks for the ReferTo practice, in the RangeToLook range - in the GDP sheet, and which instGDP to obtain.  This returns the row number of the match.

it is used like this in the worksheet:  =getgdpreferto(getsheetname($A$1),GDP!$I$1:$K$22,ROW()-1)

getsheetname($A1) returns the current sheet name - thus the ReferTo string

GDP!$I$1:$k$22 is the range of the blue cells of ReferTo area


Row()-1 just sends the requested "instance" of the ReferTo string appearing in a given row - that way we can use the result - the row number of the match, in the spreadsheet index function looking up the practitioner name.  As follows (with error checking):

=IF(getgdpreferto(getsheetname($A$1),GDP!$I$1:$K$22,ROW()-1)=0,"",INDEX(GDP!$B:$B,getgdpreferto(getsheetname($A$1),GDP!$I$1:$K$22,ROW()-1),0))

the getgdpreferto() function returns zero if there is no match found for the instance requested.

e.g., if we want the 5th instance of HHTOP and there were only 3 Referrals - the function would return 0.

BE SURE that you change the cell references from $I1:$K22 to whatever range you end up working with.  I didn't build a dynamic range out of this as this should be used like a regular function and to avoid unnecessary overhead associated with volatile ranges.

Please take a look and advise any questions.

Your solution is attached:


Enjoy!

Dave
GDP---Ref-r2.xls
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 35315882
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
0
 
LVL 1

Author Comment

by:Glyn Merritt
ID: 35364854
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
0
 
LVL 1

Author Closing Comment

by:Glyn Merritt
ID: 35364876
Rapid response and spot on target! Thank you.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35365006
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now