I have a spreadsheet containing 3 sheets. MW01 (first floor inventory), MW02 (second floor inventory), and SEARCH.
The sheets contain a part number in one column and a shelf location in the second column. I currently have over 1000 parts listed and growing.
I want to be able to type a part number into a cell on my "search" sheet and have it return the shelf location of that part.
(I am using Excel version 14.0.7106 and MS Office Professional 2010.)
I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.
Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:
'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select
[Code] ..............
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
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.
If I wanted to use excel to track inventory in/out, is there any way I can do the following?
I have a barcode scanner and I wanted to be able to quickly add and subtract quantities.
So, I want to create three fields at the top of list, one with the function to Add 1 to quantity, one to Subtract 1, and one simply to find (well I guess I can use excels find feature, but that would be an extra step)
When I scan a barcode into the "Add 1 to quantity" field (which only appears at the top of the page), excel will automatically add 1 to the corresponding field next to the item that has that UPC number. The same for Subtracting 1.
When we receive product our freight department has a voucher full of UPCs. I want to enter in all the UPCs in column A on Sheet2 in excel. I would then, on Sheet1, start scanning the actual products that we received. If a UPC that I scanned is not on Sheet2 I would like the cell to highlight in red so we can catch and remove the discrepancy.
View 7 Replies View RelatedQuick rundown, I have a sales background, so other parts (accounting, inventory, etc) are what I want to improve so Im not running around when an order comes in, where is it, do I have it in stock.
I know microsoft has some templates, any worth downloading and starting with, then move the data or add functions to an inventory and accounting template to start, any other templates recommended for an online business.
I've used excel to do a lot of managing stuff as far as budgets. I'm not sure if this can be done or how. I'm not to great in excel, So perhaps you can lead me a hand. I'm starting out a home based business and trying to set everything up so I'm ready to officially start. I am planning on getting a scanner to keep track of what comes in and out.
So what I wanted to try was if I scan "A" 4 times I wanted the QTY to be 4 so basically group similar Items move on to "B" and oh crap I found 10 more of "A" scan that and have it updated now to 14. You catch my drift? so I'm not sure if a statement like {if "A" is scanned then the QTY would be +1} Then the sales part would be the opposite of that statement.
I'm trying to use a barcode scanner to enter in information in a given Excel spreadsheet and then search for that exact same information in a column in the same Excel spreadsheet.
The barcode will enter in the numeric information in A1.
I am searching for the exact information in column F.
If found, I would like for the cell the information is in to turn a color such as green.
Is that doable with a formula in Excel 2010?
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.
View 9 Replies View RelatedI imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do...
Find MIN in Column F
After finding the MIN in F (let's pretend F222 is the current min)
Show the text in the field next to it (in this case display text from E222)
Is there a good way to find out after the exists returns True in which place in the array the Z is located in ?
...
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
Z = a(i, ii) & ";" & a(i, ii + 1)
If .exists(Z) Then
msgbox "The array location for existing Z value is ..."...
...
After I run Hlookup on a product, I would like two variables, Row and Col, to assign the actual row and column of the spreadsheet to the location in the table. I have a small worksheet/program that I have attached, what I am trying to do. The program works, but my code is very long for the little bit of information.
Sales.xlsm
I have a table with customers and their addresses (sheet2) and I need to find their GPS locations in sheet1.
I guess the logic would need to be something like this:
find CityName and StreetName in sheet1 where StreetNo is between minStreetNo and maxStreetNo return GPS X and GPS Y
with the formula or VBA code?
Example.xlsx
I am looking for a VBA Code which will:
1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
Excel has a function, everytime when I save the file, it will create a backup. how could I specify the location for my backup file?
View 8 Replies View RelatedI am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
I am using xp pro, excel 2010 and the ftp server has linux installed. To open files from server I press open button in excel, then choose ftp location from drop down menu. FTP logon box asks password with the option of default user selected. I provide password and go on.
Some days ago something happened, I don't know what but as a result of it when I opened the FTP logon box, the anonymous option was selected by default. So I selected user option, gave id password and hit ok. It did not connect. Then I removed that ftp location from excel ftp locations. Whenever I tried to add an ftp location, excel restarted stating error message " *** encountered a problem *** ". So I reached a site after some googling and was directed to add the whole ftp file location path in the "File name" box of the open dialog box. It worked and the ftp location was automatically added in ftp locations. [ any ftp setting in excel is automatically transferred to ms word as well]
PROBLEM > Now I have a different problem. I can access ftp folders and subfolders but when I select a file and click open, downloading bar at the bottom of the excel does not start and after a long wait an error message pops up " the internet address ' ftp://192.168.****/folder/subfolder/filename.xls" is not valid. [I can open ftp files using filezilla etc]
I just used the excellent formula =row(index(range,match(1,index,--(range)>0),0),0))) to give me the first instance of a number, but i am wondering if there is a formula to give me the last instance of the number as well? Maybe? This would save me much time if possible.
View 11 Replies View RelatedI have a couple of functions in a excel addin file which is located on a network, i have been having this issue on a few spread sheets now and decided to test it on a completely new blank sheet.
The function i am using to test is called "extent" and it is called fine when loaded on one machine, loads fine on another machine, saved it. Load it up on another and the link is completely wrong. It is completely random between machines.
The link it loaded is an old one i have used before that is not in use, but it was not associated with this spreadsheet file in anyway. Is there anyway to force excel not to update these links automatically as the manual option is greyed out.
I can not fathom out why excel puts in this network location.
I have attached some screenshots, the correct network location is PODDrop BoxesPOD_AdminAddins
excel-link1.png
excel-link.JPG
I am currently having a slightly annoying issue that only happens 50% of the time, that when trying to re-save a excel sheet on a network location. The network location is a folder on another computer. The same happens when I try and save it as a pdf, but only happens half of the time.
The message which pops up says something along the line of 'Saving as: 'File-ServerDocumentsexcelsheetexample.xls' ' and a sort of green loading bar appears. It does this for about 15 seconds then goes off.
I've checked our anti virus (which is eset) and I've set it to not real time scan network locations.
I have attached a example of the saving message: ExcelWarningExample.jpg
I have a text box in a user form that when the information is added and the command button is clicked I would like that information to go to this workbook, which is in a different location.
H:Burney TableMaterial That Needs AddedMaterial to be added.xls
I need the in formation to stay in Column A starting in Row 2.
The next time info is entered into the text box and the command button is clicked i need the information to go to the next empty row in the workbook
I am working on a project where i am having 8 excel files saved at diffrent location so i want to create userform which will open particular file location and from that user can select the file which he want and then can go further. so i need a coding so that user will be prompt 8 times with file location. for eg. once user select particular file from location then again this code route him to select next file from file location. is it possible??
I tried using below code but in this code when i run userform file is not opening but when i run this code mannually by pressing F8 desired file is opening i dont know what is glitch in this ? another problem i am facing is not able to understand how to repeat this steps again to open another file using this code??
VB:
Private Sub Commandbutton1_Click()
Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)
With f
[Code]....
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
I would like to have a macro button that will open the file location (path were the excel is stored) in MS explorer.
I found something like:
Sub ExplorePath()
Shell Environ("windir") & "Explorer.exe " & ActiveDocument.Path, vbMaximizedFocus
End Sub
But that give the error that an object is needed. Would that also work for MS Word?
I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)
I am looking for some code which will return the location of the form button which is being clicked on to activate the macro. I would like to avoid using an active.x button if at all possible.
In this way I can use the same macro on a column of buttons to trigger an action based on adjacent cell contents.
e.g.
Name Button
Mr a Button1
Mr b Button1
Mr c Button1
The aim is to use the button on an index tab and to capture the name next to the button as a sort string for data on a separate tab.
I am running Excel 2010 on Windows Xp.
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.