To all,
How are you? I need your help on a formula to look up and sum on different criteria. Sumifs will not work because it has to be backward compaitble to 2003 and sumproduct will not work because the ranges are different. Here is the gist of the problem:
I have a template that will be autopopulated based on data from another sheet. This will be done via VBA. The issue is that the template is person specific and based on state. But there are cases where one person is responsible for two states.
I need a formula to help me lookup the state the person resides in, and multiply it by the sum of the two variables, ALOS and Rate and payer type. The attached template will explain it better.
Let me be more specific by giving you a breakdown based on the attached template:
1. The first tab are actual admission each person generates by category. This will be vba into the template on a weekly basis after user input.
2. the second tab is the control tab where the person actual revenue targets are calculated. This is taken from a different workbook. In this tab, you will see the name of the person, their manager, the state the person resides and other criteria related to the individual
3. the third tab is data table for ALOS by State and payer type and Rate by payer type
4. the fourth tab is the template that will be populated via macro and formulas.
If you look at the 4th tab, and see the formula for the first individual, you will see exactly my issue. What happens if the person covers two states? I am at a loss.
The attached spreadsheet explains it all.
I appreciaate your help and will look for solutions as well.
How are you? I need your help on a formula to look up and sum on different criteria. Sumifs will not work because it has to be backward compaitble to 2003 and sumproduct will not work because the ranges are different. Here is the gist of the problem:
I have a template that will be autopopulated based on data from another sheet. This will be done via VBA. The issue is that the template is person specific and based on state. But there are cases where one person is responsible for two states.
I need a formula to help me lookup the state the person resides in, and multiply it by the sum of the two variables, ALOS and Rate and payer type. The attached template will explain it better.
Let me be more specific by giving you a breakdown based on the attached template:
1. The first tab are actual admission each person generates by category. This will be vba into the template on a weekly basis after user input.
2. the second tab is the control tab where the person actual revenue targets are calculated. This is taken from a different workbook. In this tab, you will see the name of the person, their manager, the state the person resides and other criteria related to the individual
3. the third tab is data table for ALOS by State and payer type and Rate by payer type
4. the fourth tab is the template that will be populated via macro and formulas.
If you look at the 4th tab, and see the formula for the first individual, you will see exactly my issue. What happens if the person covers two states? I am at a loss.
The attached spreadsheet explains it all.
I appreciaate your help and will look for solutions as well.
