Barcode Input To Match Cell And Return Value Of Another Cell
Jul 25, 2014
I get a text file daily for routed deliveries containing a barcode, name, address and phone number that I convert into csv and then upload it into a route optimization software. after optimization is complete it has assigned packages to drivers and I export it to excel and print. My sorters are having a difficult time reading the addresses on the packages and assigning them to the proper driver, so I am trying to come up with a way.
My idea is that I can import the barcode as a custom field into the route optimizer and then when I export it, that barcode field will still be associated to that order. I want to scan the barcodes of the package into column C which will reference all of the barcodes in B and when it finds the match, it will return the value in A, the driver assigned to that particular package. This way my sorter will just have to scan the packages and throw it into the proper bag for the driver instead of having to visually scan the printed manifest to match.
View 2 Replies
ADVERTISEMENT
Dec 12, 2012
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies
View Related
Dec 21, 2013
I have a value in $ED$1. This is Value X.
Row A = $DO$7:$DV$7
Row B = $DO$10:$DV$10
[Formula location = $EE$1]
Where Value X occurs in Row A, l want to return the parallel cell in Row B.
Both rows are sorted in numerical order. There is a slight complication though: Row A might contain multiple occurrences of Value X. If Value X is repeated several times in Row A, I want to find the left-most occurrence of Value X in Row A, i.e. the one closer to Column DO.
View 5 Replies
View Related
Nov 14, 2013
I am using Excel 2013 and would like to input data into my spreadsheet using a barcode scanner. I've created some barcodes using an online barcode generator (Code 128-B). My barcodes are text as opposed to numeric. I initially tested it out using Access and it scanned the information just fine. However, when I try to scan in a code into Excel nothing happens, no error, no data, no nothing. Is there something I need to set in the options of Excel to recognize the barcode scanner as the input device? Or is there some other reason why the scanner might not be pulling in the data?
View 3 Replies
View Related
Jan 30, 2008
I am attempting to have a user enter a First Name into the first Input Box and a Last Name into the second Input Box.
Within the code, I would like both answers to be returned to cell A1.
For example: A1 would read as Doe, James or James Doe (any variation is fine, as long as its one value in A1).
Sub Name()
y = InputBox("Enter First Name", "Information")
If y = "" Then
MsgBox "You must enter", 16, "Message"
Else
x = InputBox("Enter Last Name", "Information")
If x = "" Then
MsgBox "You must enter", 16, "Message"
Else
Range("A1") = y & & x
End If
End If
End Sub
View 9 Replies
View Related
Apr 1, 2014
For example i input in cell A1 April 1, 2014 in cell A2 it must put Tuesday.
View 2 Replies
View Related
Nov 25, 2013
I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.
Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:
Worksheet 1 (ColumnA/ColumnB):
Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000
Worksheet 2 required output (no row gaps or spaces):
Input cell "Expense" - in A1
List required (A3:B5):
Expense/20,000
Expense/9,000
Expense/11,000
View 9 Replies
View Related
May 30, 2014
code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.
For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.
View 1 Replies
View Related
Feb 26, 2007
I found what looks to be a BASIC or perhaps early C form of code that will do exactly this, however I am finding it out of my grasp to 'convert' the below code over to VB for Excel, and I was hoping someone would be able to piece it together for me. Code128B is a barcode font that requires a 'computed' start and end bit, as well as a checksum character(s) in order for the barcode to be valid and readable by barcode scanners. This code (in whatever it is written, I don't really know), is said to do just that. Code:..................
View 4 Replies
View Related
Jan 29, 2009
I have a condundrum that having trouble solving hoping someone may be able to help...
I am trying to return the contents of all cells that match a value based on another cell.. I have managed to achieve with the forumla below but it only returns the first value matched.
what I wish to do is return all values in ROW E where the value in ROW B equals Deliver and combine into one cell [F1].
ie.
B E F
DELIVER VALUEW VALUEW VALUEY
COLLECT VALUEX
DELIVER VALUEY
The formula I have currently uses index but only returns the first value found. I need to find all values and place in one cell. C5 holds value 'deliver'
=IF(ISERROR(INDEX( 'Salis Today'!$E:$E,MATCH(C5,'Salis Today'!$B:$B,0))),"",INDEX('Salis Today'!$E:$E,MATCH(C5,'Salis Today'!$B:$B,0)))
View 10 Replies
View Related
Apr 14, 2013
I know I can do this by nesting a load of if statements but I was wondering if there is an easier way.
If in cell A2 I have the value 12 and I want cell A1 to return a value FOUND if any cell from A6 to A24 has the value 12 in it.
View 3 Replies
View Related
May 18, 2014
I have a product list and the products have different levels. I want to lookup and return the the rows with level C and return the article number and price. If the row has level A or B I want to skip to the next level C.
I want to use a formula or array formula and not a Macro.
Data
Article
Price
Level
KL507
56
A
[Code] .......
Expected result in other table
Article
Price
JK879
98
FJ893
98
RT344
23
View 4 Replies
View Related
Jun 16, 2006
I have a column of data in column and a column of data in column F. The data in column F is exactly the same as column C but in a different order. Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H? i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27. All of the values are unique (I hope).
View 2 Replies
View Related
Oct 26, 2006
I need to pull data from Column C by looking at Column A. However it's not as simple as that. Under Column A, there are various fields, the first number represents a type of product and under what year it is currently in. So "300100" would represent a 3 yr product that is in it's third year, "210000" would be a 2 yr product in it's first year and so on. The problem is that when I pull data from the DB, some 3 yr products are flagged as products that are in their 4th or 5th years, which isn't possible. So I created an IF statement that takes this into account and ignores illogical combinations.
=If(A2="300100",C2,If(A2="301000",C2,If(A2="310000",C2,If(A2="210000",C2,If(A2="201000",C2,If(A2="510000",C2,If(A2="501000",C2,If(A2="500100",C2,))))))))
As you can see, I don't have the conditions "500010" and "500001" because it won't fit into the IF statement. Is there another way to go about doing this task? I've searched up the forums and someone recommended using the IF statement with VLOOKUP but I don't think it works in this particular case since I have 10 conditions.
View 2 Replies
View Related
Oct 9, 2013
I have a spreadsheet with 2 different sheets (DB and Workings). What i am trying to do is using VBA code is, need to match the state name CT from workings sheets (cell C3) with DB sheet of column B4 to B9 and return the Team values from column A4 to A9 in the row 4 (C4 to E4) of the Workings tabs (highlighted in Red) and similarly for the state name LA i want this process to get repeated.
View 4 Replies
View Related
Apr 3, 2014
I have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.
[Code].....
The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.
Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.
Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?
View 7 Replies
View Related
Feb 25, 2009
make an adjustment to my Formula so that if the Index match locates a cell that is blank it returns a blank cell rather than a 0. I know we need to add in =IF(iserror( but i am aving trouble..
View 5 Replies
View Related
Sep 29, 2013
I have two files. They both have US census tract numbers in a column GEOID2010. One also has a CSA2010 text description associated with each census tract.
I need to match the cell with a census tract in the first file (example1).xlsx with the census tracts in the second file (example2.xlsx), and then return the CSA2010 text from the next column over to the first file.
I've attached two example files.
View 4 Replies
View Related
Apr 24, 2012
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
View 8 Replies
View Related
Dec 10, 2007
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE:
Sweater SWTR
T-Shirt TSHT
Jeans JEANS
COLOR TABLE:
Blue BL
Green GN
Purple PL
Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
View 9 Replies
View Related
Nov 5, 2011
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
View 3 Replies
View Related
Aug 16, 2013
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
View 4 Replies
View Related
Feb 22, 2014
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
View 4 Replies
View Related
Jul 24, 2014
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
View 9 Replies
View Related
Jul 23, 2012
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
View 5 Replies
View Related
Jun 8, 2014
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
View 3 Replies
View Related
Feb 28, 2007
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies
View Related
Jan 22, 2013
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls‎
View 4 Replies
View Related
Mar 24, 2007
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related