Create A Formula To Select Selected Inventory By Month
Sep 28, 2009
I'm trying to make a simple program to allow me to total the amount of air filters I need to purchase for a given month. I am entering the amount of filters for each property and the frequency of replacement ie. monthly, quarterly, one month out of the year, etc. I would like to enter a month, ie. 2 for february and have the program list the quanty of each filter that I need to purchase for the given month. See sample attachment.
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ]. 1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"] 1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"] 1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
What I'm trying to create is a way to populate a quick inventory list with my bar-code scanner.
I've already learned that in order to have excel move to the next row each time a 12-digit SKU number is scanned, I'm going to have to utilize a userform text box.
My problem:
I don't even know how to begin coding the text box to accept a 12digit number, enter it into the next available blank cell (I'd like it to populate vertically) and then ready itself to accept the next available 12digit number.
My desired end-result:
My scanner is attached to a lengthy USB-Extension, so I can roam my store and quickly scan the inventory on display. For that reason, running back to my laptop and striking "Enter" each time makes the whole solution pretty useless.
Once finished, I'm assuming I could use excels count feature to tell me the quantity of the items I have on hand.
I am trying to create a Macro that will let me copy and paste the Inventory with the Corresponding SKU on a different spreadsheet, I have attached the Spreadsheet, Sheet 1 is the Missing Quantities and Sheet 2 has the updated Quantities that will need to be identified by SKU from Sheet 1 to Sheet 2.
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.
The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).
Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.
What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.
I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like
"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details: Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1) - $25K-100K, recognize in two equal parts in months N + 1 and N + 2 - over $100K, recognize in three equal parts over 3 months N + 1, N + 2, N + 3 ...
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
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.
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.
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".
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.
I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.
The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:
1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain. 2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab. 3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.
I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.
Column F, Row 7 (This will be a new/different number entered every day) Column G, Row 7 (Begins with a starting total of 2545)
I need Column G to add to it whatever number is entered into Column F, Row 7 and to keep the new total (ex. enter 10 into Column F/Row 7 & the total of Column G/Row 7 becomes 2555; if I then enter 20 into Column F/Row 7, Column G/Row 7 adds 20 to the original 2545, not the new total of 2555).
Column G is not keeping the new total, it reverts back to the original number of 2545.
I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
I have a large Excel project that I am working on and it is in it's final stages......i have a table with week 1 to week 5 on the top.....I hoping to be able to change this across multiple sheets to the week ending date for that month? so if on the first sheet someone selects april 2006 then week1 and so on would show 07/04/06
We have a spreadsheet that pulls data from various other spreadsheets that contain information on current year data, budget year data, and last year data. We need to pull from the current year spreadsheet information each month on the inventory count on hand.
We need one formula in one cell of the main spreadsheet that will know to pull from the appropriate month column of the current year spreadsheet based on the particular month we are running the report for.
In other words, we are not wanting to need to change the formula each month, but to write it such that it will know by our indicating in the main spreadsheet which month it is (which is always indicated in Cell B14).
In the main spreadsheet, the cell containing spreadsheet information on where to find the current year data is $D$2.
Calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.
I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.
Does anyone have a suggestion as This problem is becoming quite frustrating.
I need to delete part of the row where one cell is selected. If more than 1 cell is selected, I want a msgbox. Example: I have any cell selected on row 26. I want to select all cells in this row including column C thru column AD. Column A & B are not to be deleted. After the selection is made, I want the row deleted and the below cells moved up.
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
I want a table to display data based on which month i select from a drop-down list, the data is of course extracted from a different table. For example, in my final table( highlighted in yellow), i want to display the revenues, cost of goods sold..etc of April in this table when i choose April from the drop-down list, the data of the entire year is located in another table that i plan to hide, as we only need to review one month.
I've created a User Form to input data on a dashboard where you can select a date from a dynamic list of dates. The list of dates is calculated using:
Code: =(TODAY())-WEEKDAY((TODAY()),1)+2 The other dates are based on this date -7.
The User Form code:
Code: Private Sub CommandButton1_Click() Dim emptyRow As Long 'Make Sheet30 Active
[Code]....
This works perfectly for any date except dates from December. 3/12/12 (3rd Dec) turns to 12/3/12 (12th Mar) when it's copied to the cell. The same happens for 10/12/12 - becomes 12th October instead of 10th December.
Currently working on a project for university and still learning how to use macro. Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
I have a spreadsheet that has many shapes throughout and are connected together in a specific order. It is basically a drawing of steps and transitions in sequential order. I want to be able to select a group of cells that contain all the shapes that I want to select, run the macro and it select all the shapes within the selected cells. With all the shapes selected that I want, I can move and manipulate them as desired. The other option is to select each shape individually holding shift to make the group selection. The code that I am working with is as follows:
Sub SELECT_SHAPES_too() Dim SH As Shape Dim Rg As Range Set Rg = Selection For Each SH In Rg.Shapes SH.Select Replace:=False Next SH End Sub
This is the idea that I want to do but I am having problems with the "for each shape in selection" portion.
The User has made multiple selections with the mouse. The spreadsheet is filtered. The user will usually make different multiple selections on the following columns: A and X through to AR (inclusive).
I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:
