DarkestValkryie
New Member
- Joined
- Jun 12, 2018
- Messages
- 5
I am looking for a little help here. I'm not a novice, but I am not an expert either. Just need to resolve one issue. I will include as much info as I can during my explanation.
I am trying to make a "days since" tracker that shows the days since someone has delivered a certain thing. (not using dates just week numbers) like displayed in the table below;
<tbody>
</tbody>
I have already worked out how to get the <1 to return as 7 and (=>)1 to return as 0 as well as not recognising a blank cell as anything, I just need to find a way to reset the counter every time (=>)1 is detected.
My formula so far is this (be warned its long)
=IF(ISBLANK(D6),0,IF(D6<1,7,0))+IF(ISBLANK(E6),0,IF(E6<1,7,0))+IF(ISBLANK(F6),0,IF(F6<1,7,0))+IF(ISBLANK(G6),0,IF(G6<1,7,0))+IF(ISBLANK(H6),0,IF(H6<1,7,0))+IF(ISBLANK(I6),0,IF(I6<1,7,0))+IF(ISBLANK(J6),0,IF(J6<1,7,0))+IF(ISBLANK(K6),0,IF(K6<1,7,0))+IF(ISBLANK(L6),0,IF(L6<1,7,0))+IF(ISBLANK(M6),0,IF(M6<1,7,0))+IF(ISBLANK(N6),0,IF(N6<1,7,0))+IF(ISBLANK(O6),0,IF(O6<1,7,0))
This is per cell in the total Column and relates to that row number.
I just need to add the reset count feature to it. Macros are enabled so if I need to insert code that is not going to be a problem I just need it to work and to work in a single cell. I have a grid that does the count for me but that's no use to me. It has to be contained in 1 cell.
Many Thanks in advance.
EDIT:
Also, If I need to use the existing table that works it out across multiple cells as a base for the formula, in order to contain it in one cell then that is fine.
I am trying to make a "days since" tracker that shows the days since someone has delivered a certain thing. (not using dates just week numbers) like displayed in the table below;
Store Name | Region | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | Store Total | Annual Total | Days Since Last Appr |
Erdington | DC10 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 3 | 3 | 0 | |||||
Leamington Spa | DC10 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 3 | 3 | 21 | |||||
Coventry Alvis | DC10 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 3 | 3 | 7 |
<tbody>
</tbody>
I have already worked out how to get the <1 to return as 7 and (=>)1 to return as 0 as well as not recognising a blank cell as anything, I just need to find a way to reset the counter every time (=>)1 is detected.
My formula so far is this (be warned its long)
=IF(ISBLANK(D6),0,IF(D6<1,7,0))+IF(ISBLANK(E6),0,IF(E6<1,7,0))+IF(ISBLANK(F6),0,IF(F6<1,7,0))+IF(ISBLANK(G6),0,IF(G6<1,7,0))+IF(ISBLANK(H6),0,IF(H6<1,7,0))+IF(ISBLANK(I6),0,IF(I6<1,7,0))+IF(ISBLANK(J6),0,IF(J6<1,7,0))+IF(ISBLANK(K6),0,IF(K6<1,7,0))+IF(ISBLANK(L6),0,IF(L6<1,7,0))+IF(ISBLANK(M6),0,IF(M6<1,7,0))+IF(ISBLANK(N6),0,IF(N6<1,7,0))+IF(ISBLANK(O6),0,IF(O6<1,7,0))
This is per cell in the total Column and relates to that row number.
I just need to add the reset count feature to it. Macros are enabled so if I need to insert code that is not going to be a problem I just need it to work and to work in a single cell. I have a grid that does the count for me but that's no use to me. It has to be contained in 1 cell.
Many Thanks in advance.
EDIT:
Also, If I need to use the existing table that works it out across multiple cells as a base for the formula, in order to contain it in one cell then that is fine.
Last edited by a moderator: