Tracker
HOME    TRACKER    Excel

## A friend of mines wife has decided to get into MaryKay and he is wanting to create a spread sheet to keep track of the product she has. He wants to be able to enter the product in and at the end of a show subtract whatever product she sold with a sheet showing what she has in stock.

Related Forum Messages:
Inventory Tracking
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?

Inventory Tracking ...
So far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.

We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....

Inventory Trend Tracking
Ive got an inventory of about 2000+ items as well as items that can be broken down into more specific groups such as colors, designs and sizes. What i want to know is how to get the correlation, thats excel function CORREL(), of each item for the time weve had it. The data I can provide is the monthly quanitity sold of the item.

For example I have item A.

Sold Month
5 1
7 2
4 3
5 4

and so on. Easy enough to do. My only problem is I would have to manually do that 2000+ times for my entire inventory to figure that out. What I wants is a command that can verify my item code, which is simple enough as I can convert the item code to something as simple as a string or leave it as is. Not every item has been in the system an equally long time though. If that were the case i could just copy it with blank cells to fill the space and copy and paste all the way down. So I need to find a way to get an IF statement to correlate the corresponding cells IF the item number matches the target cell. What I have right now is something to this extent.

=IF('2'!\$A:\$A,A1,CORREL('2'!\$B:\$B,'2'!\$E:\$E))

FIFO Inventory Method Balance Tracking
I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First In-First Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.

I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.

I have a spreadsheet that can't be password protected but this doesn't mean just anyone should be accessing it. Is there a way to record who opens it and when? Ideally I could place some code into the spreadsheet that recorded on a sheet who has opened it and when. This will detract certain nosy people from accessing it!

My spread sheet reads column A teamname, B games played, C wins, D Losses, E Ties, F winning % and G GAMES BACK

I'm looking for a formula to determine games back based on wins, losses, ties and also based on the number of games played.

example, team 1 played 9 games and is 8-0-1. Team 2 has played 9 games and is 8-1-0. Team 2 is would be a 1/2 game back. Team three is 8-0-0 also a 1/2 game behind team 1 but tied with team 2.

So in column G it would read team1 "-", Team 2 ".5" and team 3 ".5"

I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.

What I want to do is have three columns that interact with eachother.

1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status

What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".

So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".

Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

Filtering For Summary: Produce A Spreadsheet For Tracking Pupil's Progress Through A Year At School
I'm trying to produce a spreadsheet for tracking pupil's progress through a year at school (Targets/Predictions etc) but am having some problems with creating a summary of data gathered for each pupil. I have attached the file for your perusal; On the data entry sheet staff will fill in the appropriate data, and on the summary sheet I am looking to generate summaries for each pupil (I have set up how I want it to look). This may sound easy, (and probably is); the issue is that there are around 30 subjects in total, but pupils will only have be doing 5 of them, I need the summary sheet to show the information for subjects they are taking only (It should come up with the subject name under the headings subject1/2 etc and the appropriate grades to go with them, missing the blanks out. Obviously I could do this by cutting and pasting for each pupil...But there will be almost 2000 pupils in the list!!!

Reorganize Spreadsheet To Comply With Vendors Inventory System
Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.

Most of our part numbers have multiple applications that they work for.

The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.

I don't know if I should be messing with formulas, pivot tables, VBA...?

Inventory Spreadsheet - Transfering Count To Master Broken?
I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.

FIFO Inventory- COGS & Inventory Valuation
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.

Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.

What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.

Eliminating Sold Inventory From The Inventory List
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...

I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.

Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items
I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.

Reservation Tracking
I manage a small motel with 6 rooms and I'm trying to write a simple program to track my customer reservations including their details. All the commercial software is too elaborate and way out of my price range. I'm slowly learning excel but this has proved beyond my capabilities.

Tracking Changes In Workbook
I'd like to make a vba macro in excel which will be just like "Track changes" feature. I'd like to track changed cells and when and who did it. (Date, User, Cell, Last value, New value). It is important to make a report in separate worksheet and automatically fill this worksheet when the user save changes.

Tracking Date From An Excel
I own a restaurant and have a inventory list. I track the price of a specific item in column E, and want to record the date i modify column E in column D. So, I change E5, I want D5 to say the date.

I want column J to reflect the total amount of all trades which were open at the time the trade on the row is closed.

(the data is sorted according to closed trade times).

So while the trade on row 4 is the first trade to close, there were other trades also open prior to the closing. I need to show the total amount in column E of all trades which were open during the time window that the trade on row 4 was open.

Change Tracking Automatically
I have a spreadsheet i would like to have changes tracked to automatically. I know it's only a matter of clicking a few buttons to get this to happen, but i was wondering if there is a way to have excel do this by default when i open the file. I would also like to have the changes highlighted automatically.

Code For Tracking Location
locate code for highlighting the row and column I am in at any given time in a spreadsheet. I work with some pretty large sheets and find it hard sometimes to know if I am on the right line or not.

Reverse Tracking Of Data
if data values are not input and are result of formula or macro can traverse back to see which macro is used to fill the data.

DDE Change Tracking
Aaron Blood has created an excellent DDE Change Tracking.

The macro, in the xls file, archive the data from top (a1) to the End of the worksheet.

I want invert the way to file/archive the data… copying all the cells already filled, a cell down and filing the top ( A1...) with the last DDE value (In the TracK sheets).

How can I do that?

The primary code is:

Sub Change_Tracker(Watch As String, TrackOn As String)

Dim NextCell As Range, r As Double, c As Double

With Worksheets(TrackOn)
r = .Rows.Count
Set NextCell = .Cells(r, 1).End(xlUp).Offset(1, 0)
End With

Tracking Changes Without Sharing The Workbook.
I have a workbook which has a database and a pivot table. I wish to track changes without sharing the work book.

Is that possible?

Also how can I keep track of the users accessed to the workbook ?

Tracking My Employee's History Of Changes.
I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.

The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters \$40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say \$16.75.

Tracking Status Of Open Work
I currently use a spreadsheet to track my work and I would like help automating some of the functions. I don't want to use macros if at all possible. I have some formulas but it doesn't do enough.
what i do is audit other peoples work and send out error notifications when needed. i use the spreadsheet to help me tack the status of the error notifications. when I send out an error notification the auditee has up to 3 chances to rebut the error so i need to keep track of where the follow up lies and when the error is finally accepted or removed (process completed).

I would like column U to tell me where we are in the process...
2.reply received (either accepted or 1st level removal request by the auditee)
3a. if error accepted- process complete
3b. if removal request-notifcation sent(by the auditor - error removed or error remains)
4.if still an error(following 3b) - reply received (either accepted or 2nd level removal request by the auditee)
5a. if error accepted- process complete
5b. notification sent (by the 2nd level reviewer - either error removed or error remains)
6.if still an error(following 5b) - reply received (either accepted or 3rd level removal request by the auditee)
7a. if error accepted- process complete
7b. notification sent (by the 3rd level reviewer - either error removed or error remains) & process complete (3rd reviewer decision is final)

I would like the V column to be the "date of last action"
since the notification bounces between people, i need to know who is currently responsible for followup & how long they've been sitting on the work. i would like the date to populate based on the manually inputed fields where I input sent & received dates for each level of action.

the spreadsheet is too wide for the post so i will put it on a website...
http://public.box.net/kingerick89367

the columns are all titled and all manually inputed except for column U which is the one that I want to autopopulate with the current status of the error notification. the formula is a IF statement but due to the 7 formula limitation It came out too simple. it's better than nothing but I want it to be more specific.

Tracking Date And Time Overlaps
I'm trying to figure out a formula that would track a student's class schedule to see if there is a date/time conflict. I'm uploading an example of a ficticious student named Amanda for you to review.

According to the example, I should be prompted that there's a conflict between Amanda's first two classes because they both meet on Monday (M) and Wednesday (W) and the time during each of those classes overlaps.

This will need to be checked per student, based on their ID in column A.

Tracking Elapsed Time With No Date
I have an assignment to audit the time it takes convert a printing press from one job to the next. I've been using a watch to take the readings in hours, minutes, and seconds.

I recorded them in my spreadsheet, as h:mm:ss, but it showed a date also. I think I finally got that resolved, but now, when I try to subtract one time from another time, I get an incorrect answer. I think I need to convert all of these times into decimals, as in 13.23.44, as oppossed to 13:23:44.

One post on page 1 of my search said to multiple A1*24, but I don't fully understand. I tried that, but 11:30 became 11.50?

I need to extract the set up time, or job to job changover time in a decimal format if possible. There are other tasks being done with those numbers later in the spreadsheet that are more conducive to decimals, than the time format.......

Call Tracking Sheet - Electronic
I am trying to turn this call tracking sheet into an electronic one. Is is possible to have check boxes in the empty cells that will allow someone to just click them as they go along and it would create a total at the bottom to avoid countless hours of counting these sheets. Or even turn "Trans CM" into a button that you can click and it would keep track of how many times that specific button was clicked.

350 People Tracking 24 Dates For Classes Each
I have a spreadsheet with ~350 people tracking 24 dates for classes each. On a seperate tab I have a macro that copies and pastes a pre-defined macro to display the due dates. My question is, can I have a macro run the calculation and put the result in the cell rather than have around 8,400 calculations on a sheet?

Job Hour Tracking With Pivot Tables
I have an Excel timesheet that the supervisor loads the time (in and out) for all the shop employees. These employees may work up to six different jobs in one day at various points throughout the day. I have no problem with calcs and formats on the daily time entry tab (sheet1). What I need is a way to track each job using a running total so that as the job is in progress we can see how we are doing against estimated hours. I thought perhaps I should at least make a weekly time summary pivot table thinking that it would make it easier to calculate, but it didn't help with running totals. The summary included (5) copies of the same daily entry tab (sheet1) just given a different name, in this case ..a simple date change and save the sheet with the date used. OK, here's the problem. How do I create a running total that would track each job? I forgot to mention earlier that each daily time entry sheet is saved and named by the date for which it represents. The weekly time summary pivot table works fine....but it only gives me the grand total of hours worked for each day...not a running total summary. I can view the pivot tables and go to the calcualtor and add the hours up per job, but that's defeating the purpose. Anyway, I hope this is clear enough to at least get some responses. Then, maybe by then I'll have a better understanding how to ask the right questions.

Time/date Tracking Only During Business Hours
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?

Tracking Changes Made To Worksheets On A Hidden Worksheet
I've developed a workbook that is designed to allow users to produce a list of aspects (one per worksheet) and edit them as the aspect changes. However, i would very much like to create a 'log' of changes. For example, everytime a cell is changed the cell reference, worksheet reference and date are added to a list on a hidden worksheet.

Creating Personal Expense Tracking Form
I'm trying to make a spreadsheet that will track my expenses. What happens is I enter in my daily expenses in a "Notes" worksheet. This includes the date, whether it's a debit/credit, and what category is it (rent, tuition, entertainment, work income etc). It looks like this

Date - - - - - - Debit - - - Credit - - - Category
1-May-06........................\$500...........Rent
4-May-06........................\$30 ..........Dining out
etc..

Then I have another worksheet called "Expense outline" which pretty much sums all expenses in each category and displays a summary. So it would show how much I have spent in total on each category for each month. Looks something like this

------------------May-----------June---------July...
Rent..................\$300
Cable/TV............\$50
Internet.............\$50
Dining out..........\$100
Entertainment.....\$50

So what I did for the Entertainment summary for the month of May was, I used a SUMIF(column of categories, "Entertainment", column of credits). This will look for the category name "Entertainment" in my "notes" worksheet, and sums the corresponding amount from the credit row. The problem is, I also want to include it so that it will automatically differentiate between the different months. Right now, when I'm choosing the column of categories for May, I select only the cells in the month of may when I'm choosing my column of categories and credits.

For example:....................

I was looking for some help on a VBA solution. I would like to run an on open event for a scheduling workbook. It should open a logging workbook, write the %username% to the first blank line in A:A then save and close the logging workbook, leaving the scheduling workbook open and ready for input.

The following UNC can be used as the location for the logging workbook....

Call Log Tracking Issues With NOW() Looking For Answer ASAP
The employees have 3 chances to contact the customer before sending out written notification. The goal is to only have the employees enter an "x" in an attempt cell which will cause the date and time attempt was made to autopopulate in another cell. I've been using an IF formula to do this [=if(A1="","",NOW()], but each time a move down to another customer it adjusts the date and time for all other fields as well. How do I lock in the date and time they marked the cell without it affecting others. This is basically how the attempts are set up. The only unprotected cells would be those under A2-C2. Example would be employee places and x in cell A3 and D3 autopopulates the date and time A3 was modified. The problem I am running into is when A4 is modified, it not only autopopulated D4, but it changes D3 to the time A4 was modified as well.
A B C D E F
1 Attempt Attempt1 Attempt2 Attempt3
2 1 2 3 Date/Time Date/Time Date/Time
3
4

(A-C are over the word attempt, A2=1 B2=2 C2=3, D is over Attempt1, E is over Attempt2, F is over Attempt3)

Spreadsheet That I Have A Lot Of Macros That Are Attached To A Customized Toolbar Saved In The Same Spreadsheet
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?

Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:

Sheet A has 10,000 records with these fields: id#, name, address, place of employment.

Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.

Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.

Multi-Spreadsheet Formula Down To Populate The Other Cells In The Total Spreadsheet
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?

The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:

='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)

The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.

Inventory Worksheet
I am trying to put together an inventory worksheet and I have a rec'd column and a usage column, followed by a total column. I want to be able to havea formula in the total column at all times, so whoever updates the inventory sheet just needs to put rec'd and used. What I am trying to do though, is have the total row for a particular month blank, unless someone enters a rec'd amount or a used amount.

Subtract From Inventory
Firstly i am running Excel 2003 (at work)

Now my problem is that i have been asked to make a spreadsheet about Printer and Fax Cartridges (Zzzzz) but i do not know how to do the sum.

this is how my Spreadsheet looks like this (cartidge amount etc all on top line of spreadsheet)
(Hp45 etc all down left hand side) ...

Inventory Sorting
I am doing inventory right now and need help sorting numbers that I have. Ex:

I have numbers like

10
1001
101
AB1394-10
AB2948-MPG
XD89-MPG KIT
F64 KIT
GSW294-HD10

I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting:

10
F64 KIT
XD89-MPG KIT
101
GSW294-HD10
1001
AB1394-10
AB2948-MPG

Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.

Inventory Count ..
I am trying to figure out a formula for doing my inventory. I have items with control numbers on them and I would like to determine how many items there are. Example Starting #12335A Ending #12445A, if you subtract 12445A from 12345A you get 10, but in the inventory you need to count the ending number as it still remains in your count, so the answer is actually 11. I have been using for example =Sum(A8,-A9) +1 to get the correct number. However, if I have additional lines on my report that do not have inventory in them, they will show an incorrect total of 1 because of the +1 in the formula. I am trying to figure out how to do this calculation without having to delete and add the formula each time inventory is added. I have people that will not go near the formulas, so I need it to total with out having to be changed.

Copy Certain Cells From 1 Spreadsheet To Other Spreadsheet Depending On Condition
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.

I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.

The information I need to transfer from Issues is: .....

Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

Fifo Inventory Valuation ...
I have a workbook with a series of #'d tabs corresponding to different pieces of inventory tracked independently (I've simplified it significantly and attached it). I would like to use the First-in First-out inventory valuation method to calculate the current cost of inventory. A manual calculation and description of the method and the proper result is included in the sample file.

I have come across numerous references to http://www.cpa911.com/read_article.asp?ID=46 here, via google, other messageboards, etc; however, when I have tried implementing it, even as described, it never seems to work! (Note: I've included the code and set it up per their instructions in the attached file...of course, unless I misinterpreted their instructions and my problem lies there...) Moreover, I'm not sure how I would implement this particular macro anyways using named regions considering the same macro would have to be run across several sheets. As a result, the named ranges would always pull data from only the one sheet containing the named range required by the macro . This begs the question of whether there a way to create unique named ranges for each sheet that would be interpreted correctly by a single macro running across multiple sheets...

If this is at all unclear, I would be more than happy to rephrase any/all parts. After all, this problem has been aggravating me for weeks, and I am at the mercy of this forum's gifted coders who may not be well versed in accounting theory.

SUM Of Daily Inventory
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--(\$A\$5:\$J\$13=A19),\$B\$5:\$K\$13)).

BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.

Calculating Inventory Days
I am trying to calculate the number of days that items are were held in inventory as of a given date. See below for some sample data.

'As Of' - the date in question
# Holdings - Using sumproduct to find items bought on or after the date AND sold after the date
Held Days - ? Total number of days that the six items were sitting in inventory as of the date.

As Of #ItemsHeld Days
5/21/98 6

ItemBoughtSoldTotal Inventory Days
19/3/919/3/961827
21/2/921/2/971827
31/2/921/2/971827
43/2/928/21/951267
55/1/925/1/971826
65/1/925/1/971826

Summarize Weekly Inventory
I having trouble to sort out and summarize inventroy products for each week. I want to sumarize all weekly inventory in summarize table. Please see attached file. Pls keep the formate as i shown in attached spreasheet.

Inventory And Profit/Loss
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.

In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.

When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.

I would like to be able to keep track of inventory by vendor as well as profit by vendor.

Using the Consolidation PT does not seem to work.

I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.

I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?

Aging Inventory Buckets
I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:

RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days

I am using the following formula:

=IF(D2="","N/A",IF(TODAY()-D2-26,TODAY()-D2-12,TODAY()-D2-5,TODAY()-D20,"31 + days"))))))

This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?

The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...