Facing challenges in Tax Calculation sheet

[krishnabsharma]

Hi,

I am preparing a sheet for tax calculation at an organization level (Same is attached herewith).
Here i am facing some challenges which are mentioned below.

Data -
There are two sheets -
1) Sheet1 - Output with all data points
2) Sheet2 - Input sheet

Problem Statement -
For an EMP, there will be "From" & "To" dates available (With last day of month) in Sheet1. I want to capture the data in Sheet1 based on dates following between "From" & "To" dates range only for each EMP.
E.g. -
1) For EMP 107, duration is Apr-13 to Jan-14. So data in Sheet1 should be captured in all the months falls between Apr-13 to Jan-14. For Feb-14 & Mar-14, all values should be 0

2) For EMP 163, duration is Jul-13 to Dec-13. So data in Sheet1 should be captured in all the months falls between Jul-13 to Dec-13. For Apr-13 to Jun-13 & Jan-14 to Mar-14, all values should be 0

and likewise.

Sheet1 column wise calculations - If month falls between given month range (From & To)
Rent Paid - Should be equal to Column C in Sheet1
HRA recd - Should be equal to HRA value from Sheet2 EMP data
50% of Basic Salary - Should be equal to 50% Basic value from Sheet2 EMP data

Please help.

 

[Jake Collins]

try attached file

 

[tobediff]

Jake beat me to it, but here is my solution for posterity's sake.
I used MATCH instead of COLUMN.

 

[krishnabsharma]

Hi Jake & tobediff,

Thanks a ton for your efforts
you guys made my life drastically easier , thanks.


Jake, I have a question for you here - I did not get the use of "(COLUMN(P4)-1)/1.5" {3rd parameter in vlookup()}. Could you please help me understanding the same?

 

[Jake Collins]

Dear krishnabsharma

The 3rd parameter is used to work out the column on sheet 2 to use. So I needed to work out the relationship between the column in sheet 1 (in your example P HRA for Jul-13) and where the HRA for Jul-13 is in sheet 2 (column J) so to save me from having to manually write each relationship and allow me to copy and fill the formulas across (I selected the formulas in f4:h4 and filled these across and down). I realised that each hRA column on sheet 1 is three columns apart whereas they are two columns apart on sheet 2 (hence the division by 1.5) and the -1 will complete the final adjustment.

Hope that helps.