Mochadrone
New Member
- Joined
- Dec 15, 2015
- Messages
- 20
Hi everyone,
Let me first start off by saying that this is my first post here on the forum. I've lurked for a while now and answered all of my questions via the helpful and extremely courteous nature of everyone responding to others plea's for help.
I'm unfortunately stumped beyond reason here and have to ask for a hand if possible.
A low down of what I'm trying to do:
I work for a homebuilding company and have been updating their spreadsheets from the dark ages. I'm attempting to create a 'workloads' spreadsheet for construction and warranty. Ideally I will have a formula that will show a count of how many houses each person is currently working on based on 2 ranges and the current month.
I have:
Table2[Projected Staking] (This is the job start date)
Table2[Assistant] (To lookup and match who the house will be counted under)
Table2[Projected Possession] (When the job will no longer be counted)
A table with A4 containing:
mmm,yy formatting.
A5 and onward downwards I'm using this:
as a way to add months.
Columns B-I have Peoples names
(F3 in Countif formula below is the Assistant's Name on the columns)
I've tried:
Obviously, the above works. Where my problem lies is when I attempt to somehow get the actual time between Now and Possession and add that to the above code.
For example:
The problem I have with what I'm trying to do above is that I'm trying to ask it to tell me a specific possession date and subtract that from A4 in order to see weather it actually falls within a valid range BUT it can't pull that date because it's trying to look along a currently untargeted range for an exact date as a reference.) (This is just my assumption as to what's going wrong anyways.)
At the end of the day, as a TEST to see if this is working properly, I'm expecting to be able to put a date earlier than I have data for houses for example, in A4 and see a few zeros in the rows below before they start populating with increasing numbers (as more houses that fall within the parameters occur) and conversely be able to do the opposite by entering a date say, 7 months in the future and seeing the current houses I have start to dwindle to zero.
I've tried a lot of things and believe me, I've made some really fantastically complex formulas before, but just can't seem to get anywhere now. Here's hoping one of you has an answer for me.
Woefully lost,
Adam
Let me first start off by saying that this is my first post here on the forum. I've lurked for a while now and answered all of my questions via the helpful and extremely courteous nature of everyone responding to others plea's for help.
I'm unfortunately stumped beyond reason here and have to ask for a hand if possible.
A low down of what I'm trying to do:
I work for a homebuilding company and have been updating their spreadsheets from the dark ages. I'm attempting to create a 'workloads' spreadsheet for construction and warranty. Ideally I will have a formula that will show a count of how many houses each person is currently working on based on 2 ranges and the current month.
I have:
Table2[Projected Staking] (This is the job start date)
Table2[Assistant] (To lookup and match who the house will be counted under)
Table2[Projected Possession] (When the job will no longer be counted)
A table with A4 containing:
Code:
=Today()A5 and onward downwards I'm using this:
Code:
=DATE(YEAR(A4),MONTH(A4)+1,DAY(A4))Columns B-I have Peoples names
(F3 in Countif formula below is the Assistant's Name on the columns)
I've tried:
Code:
=COUNTIFS(Table2[Projected Staking],">="&$A4,Table2[Assistant],Workloads!F$3)For example:
Code:
=COUNTIFS(Table2[Projected Staking],">="&$A4,Table2[Assistant],Workloads!F$3,Table2[Projected Possession],"<="SUM(Table2[Projected Possession])-&$A4)The problem I have with what I'm trying to do above is that I'm trying to ask it to tell me a specific possession date and subtract that from A4 in order to see weather it actually falls within a valid range BUT it can't pull that date because it's trying to look along a currently untargeted range for an exact date as a reference.) (This is just my assumption as to what's going wrong anyways.)
At the end of the day, as a TEST to see if this is working properly, I'm expecting to be able to put a date earlier than I have data for houses for example, in A4 and see a few zeros in the rows below before they start populating with increasing numbers (as more houses that fall within the parameters occur) and conversely be able to do the opposite by entering a date say, 7 months in the future and seeing the current houses I have start to dwindle to zero.
I've tried a lot of things and believe me, I've made some really fantastically complex formulas before, but just can't seem to get anywhere now. Here's hoping one of you has an answer for me.
Woefully lost,
Adam
