Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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
0
Glyn Merritt
Asked:
Glyn Merritt
  • 5
  • 4
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
Apologies, I posted the wrong picture.  THIS is what the summary tab GDP looks like:

correct picture - view of summary GDP tabCheers,

Dave
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dlmilleCommented:
Yes - I'll be offline for a couple hours, but will revert with that...

Dave
0
 
dlmilleCommented:
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
 
Glyn MerrittIT Project ManagerAuthor Commented:
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
 
Glyn MerrittIT Project ManagerAuthor Commented:
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
 
Glyn MerrittIT Project ManagerAuthor Commented:
Rapid response and spot on target! Thank you.
0
 
dlmilleCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now