I've been stuck on this for a while. I utilize spreadsheets at work for various actions. What I have currently works, but I'm wondering if there is a more efficient way to tackle this problem, whether through VBA or a more concise formula.
On a sheet titled 'Data', you will find the following information.
Column C is Doctor Identification Number, D is the doctor's name, E is their maximum morning procedure count and F is their maximum afternoon procedure count. Not pictured is Column A, which displays a count based off information in worksheet 'New':
This is expanded down all of Column A and generates either a 1 or a 2 based off Columns H and I. I do not like having Column A, and I sincerely hope there is a way to create a macro or eliminate the need for the column. For more information, the data goes into another worksheet 'Bookings'. To get a count of the procedures, I run the following code:
. A visual aid:
Notably, the SUMIFs runs in this fashion: Take cell E2. If the date from New!C:C matches A2, the time ends in 'a', and the doctor matches up with E1, then sum the count of procedures from the Data sheet, column A.
Is what I have the most efficient way to handle it, or can I create some function called 'CountProcedures' in VBA that returns the exact same information I need? Thanks in advance for any help!
On a sheet titled 'Data', you will find the following information.
Column C is Doctor Identification Number, D is the doctor's name, E is their maximum morning procedure count and F is their maximum afternoon procedure count. Not pictured is Column A, which displays a count based off information in worksheet 'New':
Excel Formula:
=VLOOKUP(New!$E2, Data!$H$1:$I$6, 2, 1)This is expanded down all of Column A and generates either a 1 or a 2 based off Columns H and I. I do not like having Column A, and I sincerely hope there is a way to create a macro or eliminate the need for the column. For more information, the data goes into another worksheet 'Bookings'. To get a count of the procedures, I run the following code:
Excel Formula:
=IF(SUMIFS(Data!$A:$A, New!$C:$C, $A2, New!$A:$A, E$1, New!$B:$B, "*a"), SUMIFS(Data!$A:$A, New!$C:$C, $A2, New!$A:$A, E$1, New!$B:$B, "*a"), 0)Notably, the SUMIFs runs in this fashion: Take cell E2. If the date from New!C:C matches A2, the time ends in 'a', and the doctor matches up with E1, then sum the count of procedures from the Data sheet, column A.
Is what I have the most efficient way to handle it, or can I create some function called 'CountProcedures' in VBA that returns the exact same information I need? Thanks in advance for any help!
