They request for stock replenishment from warehouse as follows:
A: 4 pieces
But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock
I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.
The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?
I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:
I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).
If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?
I have a spreadsheet which has a list of staff (column B) and work categories (row 3).
Row 4 is updated each day with a count of the incoming work for each work category.
I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....
I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.
1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block
2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.
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 trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)
I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.
Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.
I am reposting this as my previous post was deleted.. i dont know the real reason for the same...
The raw data in the attachment contains:
•C1 to AI1 refers to vehicle numbers. •Column A refers to Shift Time •Column B refers to Number of vehicles required in that particulars Shift time
•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time
•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.
•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).
• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated
I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?
I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:
where order number is unique, revenue source = "Unique" where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"
I need all of the above in formula line which will be inserted in the revenue source column
I have attached the file with a sample of the information I am using.
The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:
Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:
This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:
Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:
Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.
I did check the same issue using Excel 2007 and the memory allocation releases after each save.
I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.
For an old project which was a till system i had two sheets,a data sheet and till system sheet , the data sheet contained :
example: column A - numbering of the data (to be used with V Lookup) column B - product name Column C - price
This would then be replicated in the next 3 cells for the next data category.
Till system then had a combo box which had a cell link on the current page and data from the data sheet and then i had a price column next to i (containing V Lookup formula) the price then changed depending on the choice in the combo box.
I want to incorporate combo box's in to this new project. If i can then get some kind of stock thing i intend on then using conditional formating to colour code stock levels to show severity of needing to order etc..
I have an opening balance colum for the month(I5), then a colum for receival stock for each week in the month + total of stock received for the month(O5), also a colum for delivered stock for each week in the month+ a total deliverd for the month(U5). I wish to then calculate the closing balance in (V5)
Therefore the simple formula =(05-U5)+I5 with the product dispalyed in (V5)
opening balance 3 units(I5) receivals =0(O5) deliveries = 3(U5) closing balance should equal =0 (V5). However the product displayed in V5 is incorrect and shows 6 What am i doing wrong? what formula will give me the correct answer to this equasion?
PFA my inventory managment sheet. In transections sheet i am entering my stock in and out details. I want to generate monthly statement for particular item. I am able to get issue and receipts but not able to get opening stock on the particular day.
I am looking to create coding that once stock gets to a particular level a text box pops up to alert the user that more stock needs to be ordered and ideally I would like once the user selects ok for it to take them directly to the appropriate email template.
However, I don't want the text box to come up while we are waiting on the stock to be ordered.
i.e our current minimum stock level before placing our next order is 10,000 units. Order is placed and can be received within 10 - 14 days. During this time our 10,000 units will be used but I don't want an alert to pop up to remind the user to place an order as this action will already have been carried out.
I have attached a sample of our worksheet (GTS807) used to create quotes and generate jobsheets etc once they are orders. To ensure we have enough of a size in stock I want to carry out a check against the stocksheet (stock) which I have no problem but I cant get it to automatically update the stock once a quote becomes an order and removes the quantity from stock!
As you see the balance of stock in shown in AA13 for "115270" but is there a code to find "115270" on the stock sheet and copy the new balance back there?
I need a macro that looks at the earlier orders first and assigns inventory to them. Keep assigning inventory until either there are no more orders, or the inventory is depleted. I have tried to figure this out using formulas but it never seems to work out correctly. Here is a little sample I made to show what I am trying to do.
here is what i have but i want it to adapt it you use any company by using a list of companys in a worksheet "StockData". So the company names start in "A2" and go down the column. So i want to run the download then for the modual to go to the next company in the list without having to have a sub() for each company....
I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.
For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:
52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks
I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.
For a project, I need a way to automatically have updated stock values in a worksheet to work into various other formulas. I tried the HYPERLINK formula to the individual stock reports, but it wasn't specific enough to simply get the current value of the stock. Is there a way to narrow that formula down to get only that number, and still have it update every time I open the worksheet?