Hello all
Help needed with updating table data with dynamic range.
i have two sheets. Sheet1 has data with four columns
A. Seniority No B. Rank C. DOB D. DOR (Retirement)
- Seniority is sequential 1 - 3000
- Ranks are say A to G
- DOR - incase of retirement if rank is A or B or C then DOB + 60 yrs else 57 yrs.
Sheet2 Result
on this sheet i have a table with Ranks as rows and Jan to Dec as Columns. it populates monthwise data.
i am using countifs function for this.
range1 is Sheet1!B:B, criteria is Sheet2!rank(A2), range2 is Sheet1!D:D, criteria is (Jan 17)Sheet2!B1.
I want to add another criteria that is Seniority No with say Sheet1!A
this limit i want dynamic with user input), criteria A1. that is say i want retirement only upto seniority number 1200 (Sheet2!A1). then table should display retirement in 2017 upto 1200. at present it shows all retirements from S No. 1 - 3000 in 2017.
can anyone help with this..
Thanks
Help needed with updating table data with dynamic range.
i have two sheets. Sheet1 has data with four columns
A. Seniority No B. Rank C. DOB D. DOR (Retirement)
- Seniority is sequential 1 - 3000
- Ranks are say A to G
- DOR - incase of retirement if rank is A or B or C then DOB + 60 yrs else 57 yrs.
Sheet2 Result
on this sheet i have a table with Ranks as rows and Jan to Dec as Columns. it populates monthwise data.
i am using countifs function for this.
range1 is Sheet1!B:B, criteria is Sheet2!rank(A2), range2 is Sheet1!D:D, criteria is (Jan 17)Sheet2!B1.
I want to add another criteria that is Seniority No with say Sheet1!A
this limit i want dynamic with user input), criteria A1. that is say i want retirement only upto seniority number 1200 (Sheet2!A1). then table should display retirement in 2017 upto 1200. at present it shows all retirements from S No. 1 - 3000 in 2017.can anyone help with this..
Thanks
