Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Inventory Running Total

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

i have a workbook that we enter daily purchases in by weight, price we paid and it has an avgerage cost field also. i need one that takes all these categories and produces a running total . is this possible? im trying to take as much human error out of my system as possible. is there a way to make a sheet that clears itself each time, so its an imput only sheet? any help on any of this will be greatly appreciated


Similar Excel Video Tutorials

Helpful Excel Macros

Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Delete Hidden Worksheets
- This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for

Similar Topics







I'm going to try and explain what happens and I hope we can come up with a formula that works.

We make purchases in bulk of 10, but now we can make purchase in bulk of 25 and 50 and 100.

The price goes up 10% each time we make a purchase.

We buy 10 at 1,000, the next 10 the price goes up 100, to 1,100.

So, if we make a purchase of 50, we basically just made 5 10 bulk purchases.

So the price increases would be like this if we bought these as separate purchases:
1. 1,000
2. 1,100
3. 1,210
4. 1,331
5. 1,464
Total: 6,105

However, if we just bought 50 the first time, the price would still be the same, but one purchase instead of five separate purchases.

I'm trying to devise a formula that would take the quantity (provided by a drop down selection) we want to purchase.

We already have the various cells setup for the next purchase price and the percentage increase.

Here's the formula that I've tried to work with, but the outcome is still off.

Total Cost = ((2*A+(ROUND(B/10-1,0)*C))/2-IF(B=25,C/5,0))*B
Whe A = Current Cost, B = Amount Owned, C = Original Cost


I'm hoping that someone on here can help figure this one out.


I have a spreadsheet that I am tracking daily equipment cost on, for example

K15=E15*F15

K15 will change everyday but I would like to make L15 keep a running total, so if today K15 is $5000 and tomorrow it is $3000 I would like L15 to
show $8000


Any help would be greatly appreciated.

paleaux


I am trying to write a formula to keep a running total of the weight of exlposives we find on a daily to monthly to yearly basis.

The way we have it now is only for a daily basis and want to add the running total to it.
looks like this

number items found(A1) x known weight of explosives(B1)= Net Explosive Weight NEW(C1)
C1=sum(A1*B1)
now we want to take that NEW and have it keep adding in another column till at the end of the month we can look at it and say, "*** number".

I tried writing it as a circular number only increasing by 1 multiple per time but each time I click a different cell to update numbers in each column it continues to change the running total in the NEW with it by 1 multiple. Or once I have closed it and reopen it to start a new day it starts adding itself up
C1 is NEW and D1 as our running total
=sum(C1+D1)

Does anyone know how I can get each days total to add to the previous day total without this expotential growth problem?

so far the only think I can think of is by running with column D1 as my previous days balance with no formula in it and just a value and E1 as my running total with a formula of =sum(C1+D1) then save the file and then the next day if I have something to update on that row I have to take the value in E1 and type it into D1, ignoring the new value in E1 and then change A1 to its new value.

Any better ideas out there or a better formula?
Thanks Jacob


I have a spreadsheet that is used to enter sales, guest count, and calculates avg guest sales. This is done daily. But what i want is another sheet that will be a running total of the data. So i would enter in the daily sales, and that would be added to the running total. Each days sales would not need to be saved. I think this would best be done by using a macro, and having a button to add the sales to the running total, then clear the first sheet for the next day.


Hello All;

My apologies if this has been posted before, but I could not find an answer(yet).
What I have is a 1 sheet workbook I use as an invoice. It calculates the totals, adds tax, then a total in the bottom cell ( cell M(29)). I then click a button, it saves a copy using the name typed in and the date, prints a copy and then clears certain cells in the form for use again. All works well.
What I would like to do now is add a running total on the side of this sheet (say cell O10) that adds the cell M(29) value (the sale total) so I can at a glance see how much has been sold in dollars on all the saved sheets.
All I have tried has given me a circular reference error, or it just clears the cell each time the form is cleared.

Thank you for any help
NyteMare


Newbie needing help!!!

I am trying to create a basic spread sheet with an inventory. What i would like to do is be able to keep an idividual running total on a large number of different items.


so when i remove 1 of item1 say it adjusts the original total.

The problem i have is as follows.

=Sum(A1-A2) =A3


A2 and A1 stay i place!? so next time i remove 1 it is not a real running inventory as im basicly re-typing everytime. I would really like to just enter a number into A2 with A1 adjusting itself according to the value of A2.

Is this possible or am i talking jibberish!?????


I am trying to make a spreadsheet to keep track of my daily food intake. I am basing this on the weight watchers point system. I get a daily number of points i can use and a weekly amount of "bonus" points i can use. If I go over my daily points it comes off of my weekly bonus point totals. how do i make a daily point total column and subtract my weekly bonus points as a running total for lack of better terminology.

Here is what i have so far

Daily Allowed Daily Used Daily Remaining Weekly Remaining Weekly Bonus Points Over
44 0 44 35 35 0

These columns start in "g". Column c is my daily point totals. I can only come up with a running total for daily use by then my daily used column is off. I hope this makes sense.

Thanks for any help anyone can give
Toby


Hello

ive got confused.

If i have two worksheets (w1 and w2) and on w1 i have a database of products on ws1 i.e.
column A = product name
column B = cost per product
column C = weight

then in worksheet 2 i want to basically make an order form that will calculate the total cost of an order. I want to write the name of the product and the number ordred and have the rest of the table be autofilled with the total cost and total weight for that product. I dont want to copy and paste from one sheet to the other. Also i dont want to show unecessary data from ws1 on ws2 i.e. cost per product and weight are not needed, just the name and the total cost and the total weight. this rules out my earlier idea of copying and pasting from one ws to the other. There must be a more elegant way. Does anyone know of one?


Any help appreciated.


Hi all.

I'm relatively new to Excel and especially to macros. The spreadsheet I'm working on deals with inventory levels, and I want to write a macro to connect two worksheets. The first sheet is updated weekly with new purchases, and the first sheet is supposed to be a cumulative total of inventory. So, I am trying to write a macro that would add the quantity on sheet one to the quantity on sheet two, and then clear sheet one. This will be done every week to keep a running tally of inventory. Any idea how this could be done?

Thank you for any help, and I'm sorry if a similar question has already been asked.


Hi,
I'm running excel for mac 08. I run a wholesale bakery and am trying to build a spreadsheet to keep track of what my clients order. I'd like to do these things:
-keep a running weekly total (currently it's on the 'invoicing tab')
-be able to zero out the 6 individual-day invoices at the end of the week

I currently have a total for each individual day, but what i would like to do is have one input tab that has the day's orders. at the end of the day it gets zeroed out but is recorded on the invoicing tab, which keeps summing for the whole week. then i make the invoices and zero it out.

i'd also like to translate this to my inventory. basically, i would solve all my problems if i could figure out how to keep a running total, and how i can zero out the inputs for that total when i need to. my wrkbk is attached, please ignore the first and second sheet and start on the sheet marked 'tuesday'. thanks!!


Using 2003 for awhile now, but running into a brick wall. Have a client with an inventory workbook with 156+ worksheets. Each sheet represents an artist with columns of: title & medium, size, received, date, price, date sold, invoice, paid, comments, balance, type. There is a summary sheet we created to show how much is in inventory for each artist. That was easy enough. Just ran a total on each worksheet, then had the summary sheet read that worksheet and cell.

Now we need to figure out year to date sales for each artist. I have been searching the Internet and my Excel 2003 for dummies book, but no luck on how to pull up that info. The summary sheet shows only the artist name, amount in inventory, and year to date sales. I want to be able to tell the summary sheet to read the artist sheet for a date sold range, then pick up the paid amount. One problem is that inventory that came in two or three years ago might not sell until this year, so there are varying dates in the database. I think I need to SUMIF or COUNTIF my PAID column (which is a dollar amount) and the IF would be if the range of DATE SOLD column is between 1/1/06 and 12/31/06, but I can't quite make it over the hurdle. If there is anyone out there who can help, I would greatly appreciate it.


First off I want to say thanks if anyone can help me. Thank!

I have an inventory sheet that has always been in word but I put it in Excel and have one problem.
The last three columns are price per unit, numbered ordered and total cost. All I want to do is set it up so that when I enter a number ordered it will automatically calculate the total cost. I know how to do each row individually but not how to set it up so that all you have to do is enter the amount ordered and automatically get the total cost.

I hope someone understands what I am trying to do and can help because I know it has to be something pretty simple to do.

Thanks so much,
Sindy


Hi all,
I am developing a worksheet with multiple client (Smith, Jones, Doe) charges throughout the month.
My columns are; A1 Client, A2 Amount, A3 Price, A4 Extension.

Clients order items throughout the month. At the end of the month I'd like to sort the work sheet by client and add cells that total purchases by client or, I could have a column that keeps a running total by client, which ever is easier.
Any help is greatly appreciated.
David


Hi,

I am having issues creating a workbook that will be used for time/code tracking.

I created one sheet that has all the codes that I will be using and then created another sheet which is were the time data will be imput. I would like to type the code in on Sheet 1 and have the description pulled from the cost codes sheet. Lastly I would like to have the last page Total the total amount of time charged to each code at the end of the week.

I have included a copy. Any help would be greatly appreciated.

Thanks.


I am putting together a medication inventory sheet. The fields I am trying to
calculate are as follows:

C Units Issued
G Units Added
H Total Units in Stock
L Yearly Total

What I would like to do is keep a running inventory total. I want to have
the Total Units in Stock to be the sum of the Units Added minus the Units
Issued which I can do. However,I need to then have the Total Units in Stock
remain constant and be continually updated after each calculation is
performed. As of right now every time I change Units Added or Units Issued
the Total in Stock changes only to reflect the two new number I entered. I
need to have the Total in Stock reflect the sum of the current Units Added -
Units Issued as well as the sum of the previous Total in Stock calculation.
Any help is greatly appreciated.





Hello,

I am by no means an expert in excel, but can navigate through it pretty well. I have two workbooks for inventory. The first work book is my daily sales report which gets cleared out and started over everyday. It links to my inventory list workbook by item numbers. So when I enter a product dold for the day it finds it in my inventory workbook and subtracts it from the inventory. However when I clear the daily sheet, the inventory reverts back to zero also. I have used vlookup and sumif functions throughout. I need to find a way to keep the numbers running consecutivley after clearing daily sheet. If marcos can be avoided that would be great. Is there a function I can add to my sumif that links to my daily sales? Thank you for any help. May seem trivial but I can not figure it out.


I have a spreadsheet that allows employees to enter time off taken (hours & minutes) and record the time paid back. The total paid back time is deducted from the total time taken. I have added a column that includes a running balance of time owing to the company. There are several blank rows below for the employee's data entry, and these rows include the running balance on every row.

My formula in M2 is =ROUND((K2-L2)*1440,0)/1440 and in M3, etc, my formula is
=SUM(M2+K3)-L3

K L M
Total Taken Total Repaid Running Balance
3:00 0:00 3:00
0:00 2:00 1:00
0:20 0:00 1:20
0:00 0:00 1:20 *should display 0:00
0:00 0:00 1:20 *should display 0:00
0:00 0:00 1:20 *should display 0:00

My question is, how can I modify the formula so I can eliminate the repeating of the running balance in the unused rows? I want the running balance column to display 0:00 on rows that do not yet have data entered into them. I think I need to add an =if formula to my existing formula. Am I right? and what is the format?

I appreciate any help I can get with this.

Thanks


In the attachment I have Sheet 1 and 2.

Sheet 1 contains the value of the age and weight for individual people. Sheet 2 will contain the total amount of age and weight.

Can anyone teach me how to use the MATCH formula in excel here.
For instance ( To get the total weight, the formula will try to Match the string "Total Weight" and check if its in Sheet 1 and get its value which is the Total Weight of 98 and print it in sheet 2 total weight.)

The main reason for this is because I need to know how this matching work and how does it extract the data from different sheet/workbook. I don't use linking lets say =A2 etc because formats may change at any point of time.

Thanks!


Hi Folks,

I am working on creating a worksheet for my personal finances and stuck trying to do the following task (see below).

I have tried conditional formatting and if statements within the cells with no success, i am not to clevere on if statements!!

This is what i am trying to do:

Firstly:

Column A represents Payee
Column B represents Payment Date
Column C represents Amount to pay per month
Column D to O represents each month January to December etc

The rows start from row 3 to 15

Row 17 represents the total for each month so puts a total in the cell underneath each column.


What i want to be able to do is when i enter the word PAID into a particular month in any row i would like to add the amount as running total.

For example:

If i enter PAID in row 3 Column D (January) then i what the calculation to take the monthly payment figure in row 3 column C and add it as a running total in row 17 column D.

This means that i wil have a running total in row 17 column D of everything i paid in January.

I hope that all makes sense.

I would really appreciate some help and guidance with this.

Thanks


I have a spread sheet and have some caculated fields. There is a field where the user enters the monthly collections each month and then there is a field which keeps a running total of the monthyly collections.
If I enter the monthly collections for January as $125.00 the running total for January shows as $125.00. Since I have not entered a collections for Feburary-December the running total fields is showing $125.00 in all those fields also. Is there a way to stop this.
January collections are on Line D4 and the Running total is on Line E5. I wish I could attached the sheet but I don't see how to do that.


I would like to keep a running total from sheet to sheet adding in current totals to the previous total. I have two separate cells for the calculation of the weekly total and one for the running total. My problem is that that I do not have a formula to add in the current total to the running total without me doing this each time manually. I am using this for weekly services for the year. Each page is designed the same (copied and pasted) so that each cell is the same on each sheet. Is there a formula to add the current to to the running without having to do this manually each time?


I have an excel PO form on one sheet and and inventory list on another sheet. The inventory list is over 5000 items. the po has these cells: Item#, Desc, cost, qty, total. At present I have to search the inventory sheet for each item I want to order from a vendor, which is sorted on the item#. Once I do that I have to then copy and paste to the PO. The inventory sheet has cells: item#, Desc, Cost.

What I want to do is type the item # into the item# cell on the PO and have the formula populate the desc and cost cells from the inventory sheet, I then enter the qty and my simple formula calculates the total. If I have 100 items to order you can see how this would become very time consuming.

Thanks D.


I'm keeping a worksheet that computes running totals for day's receipts. It
totals amounts from cells a2:a5 and stores that total in cell a8. The next
day takes the total from a8 and adds the sum (b2:b5). And so on. My question
is, how can I make this running total column appear empty for days that have
yet to be entered?

TIA,

Jim





I'm making a shipping log that is in calendar form. All 12 months in separate worksheets. For each day there is a 4x12 table and totals at the end of each week and month for the amount of weight sold.

Now I need to figure out a way to keep a running total of the dollar amount that is taken in. I have a separate workbook with clients, the products they order, and the cost of that product. So my questions a

1) How do I connect my shipping log to this workbook? I'm guessing a PivotTable would be the right answer, but I know very little about them.

2) How do I get a running total that is consistent with the orders that are entered into the shipping log?

Any help would be great!!

hi

iam looking for a work book to run a bussiness .
to do the following:

invoice-- quanity,price,total for line,amount paid , balance. up to 6 product lines
and to keep all previous customer invoices preferbally in one sheet. seperate for each customer

inventoy- each time an amountt from a line is sold automaticly subtract from it.
to allow when new stock arrives to edit inventory

expenses-- date, amount,for,total,and total amount of expenses, then minus total amount customers paid, equals

paid not paid- amount paid by customers and any balances not paid from invoices which automaticly updates it self

i would very much appricate any help any one can give me.
iam not asking any one to make it for me but iam sure some one here has made one for them selves if they can maybe give me the template they used for it. i have a work book like the one above which i have mad but its all manually i want something more automated. thanks in advance

or maybe theirs other software which can do the above?