Hello,I am tweaking my cycle count program and have attached an example of what my spreadsheet looks like. What I am looking for is to track my days since my count so that I can reissue or check with inventory on where they are with the count. I would like to see a way that I can either have a "follow up" or a "wait" visual but not necessary, just some way for me to know that a count is out there and it has gone over 5 days of being issued. The other problem I am having is when I have a count go beyond 5 days, but has been counted I cannot filter it out.There are 3 scenarios that can happen: A count is out there and is less than 5 days and I should wait. A count is out there and is over 5 days and I should follow up. The final(which is because the way this is set up currently) Is the count has been counted, but it is technically over 5 days and it still shows up as "follow up". I am looking for help maybe over thinking this section of the spreadsheet.If I am not explaining myself enough please let me know and thanks for the help.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Inventory help
- Thread starter Cliving
- Start date
So I think I understand what you're looking for, see if this helps.
All I did was create conditional formatting for column C. If you select on cell C2 and then click Conditional Formatting>Manage Rules, you can get a look at the formula I used.
The two formula's I use for the conditional formatting are: =AND(C2>D2,F2="Follow Up") & =AND(C2>D2,F2="Wait")
If the first is true (Days Since is greater than Days Allowed & Column F="Follow Up"), the cell is filled with red.
If the second is true (Days Since is greater than Days Allowed & Column F="Wait"), the cell is filled with gold.
If neither are true, the cell remains with no fill.
I also converted row F cells into list validation which only gives the user the option to select Wait or Follow Up. This will prevent you from accidentally typoing in that column and potentially causing the conditional formatting from malfunctioning.
The validation list is found in cells K1 & K2. If you want you can either move that list to another worksheet or just hide the columns so you don't have random data just floating around in view on your primary worksheet.
Let me know if this helps.
All I did was create conditional formatting for column C. If you select on cell C2 and then click Conditional Formatting>Manage Rules, you can get a look at the formula I used.
The two formula's I use for the conditional formatting are: =AND(C2>D2,F2="Follow Up") & =AND(C2>D2,F2="Wait")
If the first is true (Days Since is greater than Days Allowed & Column F="Follow Up"), the cell is filled with red.
If the second is true (Days Since is greater than Days Allowed & Column F="Wait"), the cell is filled with gold.
If neither are true, the cell remains with no fill.
I also converted row F cells into list validation which only gives the user the option to select Wait or Follow Up. This will prevent you from accidentally typoing in that column and potentially causing the conditional formatting from malfunctioning.
The validation list is found in cells K1 & K2. If you want you can either move that list to another worksheet or just hide the columns so you don't have random data just floating around in view on your primary worksheet.
Let me know if this helps.
Attachments
Hello Joecam,
Yes, that does give me the indicator I was looking for. The only thing now is I can use my filter to grab anything under 5 days, but for things over 5 days and they do not have a date in the date counted column, I have no way of filtering that information. That is why I was trying to come up with some if statement for the item status column in order to tell me visually, "hey check that count it has been over 5 days and has not been counted". Then, a simple filter would do the trick.
Thanks for the quick response and help on this issue.
Yes, that does give me the indicator I was looking for. The only thing now is I can use my filter to grab anything under 5 days, but for things over 5 days and they do not have a date in the date counted column, I have no way of filtering that information. That is why I was trying to come up with some if statement for the item status column in order to tell me visually, "hey check that count it has been over 5 days and has not been counted". Then, a simple filter would do the trick.
Thanks for the quick response and help on this issue.
Sorry, I'm not sure I understand what you're looking to do.
Would a filter on "Dates Counted" that shows only rows with blank information in column G work?
You could also apply similar conditional formatting to column G so that dates in that row that are more than x number of days in the past from today are highlighted a special color.
Would either/both of these resolve your issue?
Would a filter on "Dates Counted" that shows only rows with blank information in column G work?
You could also apply similar conditional formatting to column G so that dates in that row that are more than x number of days in the past from today are highlighted a special color.
Would either/both of these resolve your issue?
I figured I was not being clear enough.
So, I have reattached a portion of my spreadsheet. There are a few scenarios that can play out that I want to be able to sort through. If a count has been issued, but is under a 5 day period to count, I would like to see a "wait" or some obvious sorting method. If a count is over 5 days, I would like to see a "follow up". But if it is over 5 days, and has been counted I would like it to say "Already counted" or something. Does that make more sense? Otherwise anything over 5 days says "follow up", even if the item has been counted.
I hope this makes more sense.
So, I have reattached a portion of my spreadsheet. There are a few scenarios that can play out that I want to be able to sort through. If a count has been issued, but is under a 5 day period to count, I would like to see a "wait" or some obvious sorting method. If a count is over 5 days, I would like to see a "follow up". But if it is over 5 days, and has been counted I would like it to say "Already counted" or something. Does that make more sense? Otherwise anything over 5 days says "follow up", even if the item has been counted.
I hope this makes more sense.