My organisation has ten small offices within a ten mile radius.
Most of the items to order are office supplies, along with order forms, catalogues, brochures and pro-formas.
Each office does not order the same items.
I would like to make a ‘generic’ supplies order in excel.
Column A = ‘in house’ code
Column B = Description
Columns C to G are size, colour, pack size etc.
Column H = quantity held
Column I = quantity required to hold
Column J = amount to re-order.
I would like Excel to calculate
Amount to bring stock to hold - “=I4-h4” (in J4)
If Current Stock is more than Stock to Hold then ‘0’ is in J4, “=IF(H4<I4, I14-H14,0)” (in J4) I think.
If H4 is left blank then J4 to show “0” or ‘Blank’. (experiments have given J4 a figure of 25)
I have tried putting two formulas together, but Excel tells me I have ‘too many arguments’.
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
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 would like to use this database list I export from another program to excel format. What I want to do is to change in the C column.
The value under Akt.saldo is how much of a given article we have in stock (saldo). And the Rest. in column B is the reservation, how much of that article thats is going to be shipped at that day. ex 14161 year(14) week(16) day(1).
So as you can see in column C the Rest. value never subtracts from the stock. What is want is something like that below.
Here is just selected all the values under stock and moved to the next column. Then used white color to hide it.
Here i subtract the hidden value with the reservation to be able to see how much is left after that order.
And the same here. Subtracting next order with the current stock.
How do i do this with a macro? The list is changing everyday.
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
ive got my highly expensive office enterprise 2007 disk what is the best way of installing this so i can still use 97 and 2007. any tips? i wont bother with outlook but i'd like to try the rest ,mind you onenote,infopath and groove are a complete mystery!
how to change language of MS office.English language is not showing in options.Do I need to purchase language pack?I am using english language other than office.
I'm having a bit of an issue with OWC in 2007. I know they have been deprecated but I was under the impression, form many places including MS themself, that you could install them. I downloaded this file
All seems to go well and there seems to be a appropriate .dll in c:Program FilesCommon FilesMicrosoft SharedWeb Components111033 by the name of OWCI11.DLL. However when I attempt to add a reference to that file in a VBA project in Excel (it's one that has an old broken reference to 2003 OWC) by browsing to that file it tells me
"Can't add a reference to the specified file"
What have I done wrong? Is what I am attempting, getting 2007 to use OWC, even possible? Note I did not write any of the code for this Excel Add-in so have very little idea about what OWC are for.
I have recently updated to Office 2007. I have about 50 forms with VBA in them (all excel) and I was careful to save them all back to 2003 office files. When one of my users opens the files and uses on of the internal userforms it errors out saying it is missing a reference.
I got a copy of Office 2007 and I am having some trouble running an Excel macro. It gives a 405 Error message. This macro runs fine on my Office 2003. Is there any extra setting that I need to do in the Excel 2007?
I create a vb script file using SAS that will open excel and create pivot tables. It works in XP but not in office 97. I am not a vb expert. Here is the code.
Im looking to make a formula that states if b3 =10 then a. if b3=12 then b, if b3= 14, then c, if b3=16 then d. A, B, C, D are referring to links to another sheet.
In certain spreadsheets I have noticed a significant deterioration in save times. There may/may not have any formulas or macros on them and most are very small files 20-50kb. I notice that Excel puts a temporary file on the desktop while saving the original. The temp file goes away once the original is saved. While saving, I get the time icon which goes to beachball and it can take up to a minute to save the file. This happens on 2-3 Macs on network. Using 10.5
I have a scorecard created in excel 2007. All my end users are using excel 2002. Now I want to build a scorecard in Excel 2002 like what we have in excel 2007. Is it possible.
I want to display the traffic signals and 5 level indicators (arrows), based on conditional formatting.
This is out of box in excel 2007. But how do I achieve this in excel 2002? Is there a simple method?
Is it possible to determine the Version of Office / Outlook that is installed on a machine, an based on that, add a reference to the correct Microsoft Outlook com object library programatically?
I'm opening an Excel file that automatically attempts to update links when I open it, but I am not prompted with the choice to cancel, or update links, when I open the file, like I was in Office 2003.
I have checked the Excel Options, Advanced, General, 'Ask to update automatic links' box, but I still don't get the dialog box when I open the file.
The file is just riddled with VALUE errors. A colleague of mine who opens the file in Office 2003 can see all the data fine.
Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...
With Sheet1 .Protect Password:="******", UserInterfaceOnly:=True .EnableOutlining = True End With
It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.
Our office has different versions of Office on different computers. We have a file containing macros specifically macros that interact with Outlook requiring the Outlook Object Library. If a 2013 opens and saves the file all the libraries get changed to 15.0. Then a 2010 Office opens the file ... the Excel Objects and Office Objects libraries change to 14.0 because of 2010, but then we getting the error about missing library. Instead of loading the Outlook 14.0 Object Library like it's supposed to the systems tries to find the 15.0, but can't find it and throws the error.
I have to manually go in to References and uncheck the Missing 15.0 outlook library and find and check the 14.0 one and click ok then the macros work fine.
My question is why do the other object libraries automatically change depending on the version of office and the outlook one doesn't.
Interestingly enough this doesn't happen the other way e.g. 2010 to 2013. Ones the file is saved using the 2010 with the 14.0 references 2013 has no issues converting those references to 15.0.
I created a macro that will do a subtotal and copy the value then undo the subtotal and delete all the values(i dont need the actual raw data, just need the subtotal). i noticed when i do a copy, i have to paste from the office clipboard. is there a macro i can use to paste from the first item on the list?
I have a Access database as a frontend sending data via transferspreadsheet to Excel. All works well in the A2k and Excel 2k in which it was built. I need to distribute to other users and some have Office 2003 which has caused some problems with Object Libraries but I have made a modified XL2003 version to load on those systems. However, I get a Run-time error 1004, "Application defined or Object defined Error" and the debugger stops on the following line of code that should be underlined ....
I have an older laptop with XP and 2007 Office / Excel this works, with the new laptop doesn't work?
basically - I open a new spreadsheet - blank - just basic
I used to be able to click the data tab, then click From Access, then enter my URL to an access database on my website and BAM my data would populate in cell A1
with VISTA - I get the same error over and over again, tried a different XP machine, no problem? What gives?
error box states: Microsoft Office Excel cannot access the file [url] there are several possible reasons.
the file name or path does not exist the file is being used workbook has the same name
I'm starting to really hate VISTA after about 3hrs with this new laptop!
In the following thread, richphillips wonders about Application.Filesearch Application.filesearch In Office 2007
Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function
Function CreateFileList(FileFilter As String, _ IncludeSubFolder As Boolean) As Variant ' returns the full filename for files matching ' the filter criteria in the current folder Dim FileList() As String, FileCount As Long CreateFileList = "" Erase FileList If FileFilter = "" Then FileFilter = "*.*" ' all files Debug.Print CurDir strFolder = BrowseForFolderShell(, , , 0) If strFolder = "" Then MsgBox "You Cancelled" Exit Function End If............
I am trying to have a column that returns either "secure" or "re-assignable" depending on whether the employees title matches the appropriate office/ space allocation.
e.g. an employee who's title is "CEO" and office type is "Large Exterior Office" would return a secure. However an employee with a title of "Admin Assistant", who's office type is "Large Exterior Office" would return "re-assignable"
I have a shared spreadsheet that uses a column with a validation list. I want to be able to take the MS Office Username contained in Tools > Options to be put into a cell A1 when changing the value of A2.
