creating a cycle count schedule

SDKBRK

Board Regular
Joined
Feb 1, 2003
Messages
162
I need to know is there a easy way to create a cycle count schedule. This is what I started with COL A - how I want to count the item (monthly, weekly, bi-yearly etc). COL B - the class and item #, example 050-6125, and I need to count around 35 items a day Monday thru Friday. Last year was the 1st time we setup a complete schedule for the year and it was a pain. I used the spreadsheet that list how I want each item and then I set up a spreadsheet like this.

TAB - 1ST QTR
COL A,
Fri 1/2
050-6125
050-6124
030-7711

COL B
MON 1/12
050-8132
050-5555
030-4411

It took me awhile and this year I'm trying to delete items that we no longer have and add the new items. Any suggestions would greatly be appreciated.

Thanks.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
IMHO you are using the wrong approach. Excel and Access are set up to make the handling of data tables easy by making some very powerful functions available - as well as pivot tables. So, in this case, your data can consist of 2 columns (you can add more later as required) :-
Date Item
1/2/04 050-6125
1/2/04 050-6124
1/2/04 030-7711
1/12/03 050-8132
1/12/03 050-5555
1/12/03 030-4411

The usual method is to keep the data in a sheet of it own, and use other sheets for analysis.
 

SDKBRK

Board Regular
Joined
Feb 1, 2003
Messages
162
I started out with the the item in one column and how often I want to count it. Col A 030-1111 Col B monthly. Is there a simple way to get it broken down on a calendar.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Not exactly sure what you want to achieve. Might help if you show something roughly of what you expect the result to show.

Using my example, try making a new column which, assumimg you have a date in column A, you copy down the formula like :-
=MONTH(A1)

You can then use Data/Autofilter or make a pivot table using this column, or add more columns with formulas etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top