I'm using excel as an inventory database but need to know how to change the number format according to what the user is keeping track of. For example drums, bags , cylinders ect...
How can i change the number format to display the unit of measurement added by user?
I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.
Vehicle
Oil Filter #
Also used with
Dodge
51515
Honda
54565
Toyota
51515
I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.
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 have an inventory list (non-sequential) and I want to pull only the items with id codes that are with in 4 different ranges.
I would like to populate column A with the results, search paramiters are in Q3 and S3.
I can get it to work on a small scale but my inventory list is 10143 rows long.
I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days.
I've seen a number of formulas around... and honestly am not sure I'm on the right track.
On the attached I have used the following:
(Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)
I am having some difficulty with a macro. I have a column that contains a series of inventory numbers. I need to break it up into three parts and then have each part put into a new column. Here is an example of the data:
Column B = list of inv no.’s such as “ABC123.45” I need to divide it into three parts and put each part into a new column … so a new column X would get the “ABC” part, a new column Y would get the “123” section, and a new column Z would get the “45” ** ( I don’t need the decimal between 123 and 45) .
I'm trying to filter data into a cell that meets certain criteria...
I would like to count the number of times a sku is found in each region in each month... daily inventory counts are recorded.. the date is recorded as MM/DD/YYYY...
is sumproduct my solution? I'm getting errors, specifically #NAME?
=sumproduct((sheet1!L:L=SKU)*(sheet1!M:M=Region)*(sheet1!C:C>=1/1/2009)+(sheet1!C:C
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 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 RelatedOne of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
View 5 Replies View RelatedI have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
How do I convert 11.20.00 in A1 to 11:20:00 in B1?
I've tried =TEXT(A1,"hh:mm:ss") to no avail.
I want to convert number format to text format, any formula ?
View 6 Replies View RelatedI'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
i am looking for excel custom number format for 0 (zero) number that make center alignment..
for example ;
sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)
how make center alignment with custom number format for 0 (zero) number..
I received a request from a coworker regarding custom formatting some numbers in his spreadsheet. Those numbers are serial numbers of 20 characters long. Sometimes in my files I use this custom number formatting ###0 and its enough for the data I handle. But when I tried to use it in his spreadsheet, the following shows:
8456891070060510000
The cell must look like this: 08456891070060510302
The reason to have it like this is due to a Delivery Program requirement to deliver Set-top Units for repair. The Delivery Program do not recognize other format than the above. My coworker takes the data from a spreadsheet, and the spreadsheet needs a custom number format to display the correct number.
find a custom number format to be able to display as my coworker need it??
Text to Number or General.xlsx
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.
View 8 Replies View RelatedI have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula
e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).
how do you place 100/0 so the /0 stays static and the 100 can be used to operate? (ie: 100/00, 101/00, etc)
View 5 Replies View RelatedI have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies View RelatedIn Excel 2003, I have a series of complex(ish) lookups which populate cells in Sheet 1 from other sheets in the same workbook. Some of these cells are %ges some are not, I need to display the resultant sheet values as %ges or not based on if the cell to the left (also populated by a complex(ish) lookup) contains the character "%".
I cannot just format the cell on its own, as the list in itself is volatile and can change according to the complex(ish) lookup,
The main problem with this is I need the result to be a numeric value and NOT text in ALL cases as further calculations will be undertaken on them.
In simple if it is a %ge I need to see the %ge and use it as a %ge or if it is just a number see it as a number and use it as a number.
e.g
A B Result Req
Satisfaction with telephone access (%) 0.900032390.0%
Ability to see boss within 48 hours () 0.889 0.889
I need an excel file to do this:
i have one column like this:
492
500
773
738
572
492 When I repeat this number I need that the first (492) be formatted with a color and continuous like this
200
572 format the first 572
492 format the next 492 but the actual number stays without formatting!
I cannot figure this out, but does anyone know the custom format code to do a number into 1st, 2nd, 3rd, 4th, 5th, etc.
View 2 Replies View RelatedI want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
I'm looking to see is if there is a way to modify this code to format a phone number as (999) 999-9999. Right now it formats phone numbers as 999-999-9999.
VB:
Sub FormatPhoneNumber()
'Purpose: Formats a telephone number as 999-999-9999.
'In cases where more than one phone number is enter in
[Code].....
If I have the following info:
A1 = 5
A2 = -4
A3 = 0
How can I use Format Cells > Custom to have them display as:
A1 = 5 up
A2 = 4 dn
A3 = Level
I've been trying and trying but keep getting a message that I should use one of Excel's pre-defined formats.