Lookup: Picks Up If A Variation Number Has Been Entered
Aug 11, 2009
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
View 5 Replies
ADVERTISEMENT
Aug 6, 2009
I am trying to find a solution to a vlookup issue. I have attached to this post a sample excel file which will make it easier to understand. I have data in one table (Table 1) where the records consist of addresses with street field, city and zip. These records have to be flagged based on the street address (number and street name provided the city or zip is the same) with a flag called "Type"(as an example Type 1 or type 2, etc).
Than I have another table (Table 2 or the lookup table) that has unique adrresses with the "Type" field that needs to be brought over to Table 1. The issue that I run into is that addresses in Table 1 have variations such as 25 Main Street can be as 25 Main St., or 25 Main St, or 25 Main st., Apt 2, which all should be recognized as 25 Main Street. All we care is to flag 25 Main Street with it's appropriate flag from Table 2.
Data in Table 1 can be in the 100k or more records in some cases so any manual intervention would be very time consuming and not efficient. Now I've tried doing lookup only on the 3 or 4 left characters of this field but than you get thrown a curveball when the address is for example 25 Maitland Avenue, which will falsly flag them as 25 Main Street. Plus house numbers can be 25 Main, or 255 main 2554 Main or 2 Main, so it is not functional to use the left function nested within vlookup. This issue seems to be more of a logical validation, which I don't know how to approach. The best way to think about this is when you go print a priority label on the USPS website and they correctly validate your address eventhough you might have mistyped your address.
View 2 Replies
View Related
Jun 16, 2014
Using the text to columns option on a comma separated file in csv format leads to the right preview in the text to columns wizard.
The column titled "ATTIC: Zone ..." shows the desired format in the preview window. Please look at the screenshot 1.jpg.
After pushing the finish button to obtain the result the number format gets suddenly changed and differs from the preview.
Again check for the "ATTIC: Zone ..." column as reference: 2.jpg
Is there a menu where one can look up, or specify how to format data to force the right comma placement? What settings might be wrong?
View 3 Replies
View Related
Feb 21, 2014
I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","8),countif('Tuesday-No Shows'!D:D,"*" & A2 & "*"),countif('Wednesday-No Shows'!D:D,"*" & A2 & "*"),countif('Thursday-No Shows'!D:D,"*" & A2 & "*"),countif('Friday-No Shows'!D:D,"*" & A2 & "*")))
The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Tuesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Wednesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Thursday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Friday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*"))))
A2 designates the students first name and B2 designates their last name. If I eliminate the OR and resort to multiple COUNTIF's, it will generate twice the number of responses that I want, I fear.
Example.xlsx
View 6 Replies
View Related
Jun 22, 2007
In sheet1, (B2 & C2) are the numbers entered by the user. After they entered, it will create rows of table according to the number of groups they entered on the respective sheets.
how do I let it create the groups itself?
View 9 Replies
View Related
Jan 28, 2013
I have the following table in excel:
Price: Percentage:
$150 10%
$100 12%
$50 14%
I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.
View 3 Replies
View Related
Feb 17, 2010
I have created a workbook for keeping records of all maintenance to vehicles in our fleet.
I have a sheet called Vehicle maintenance input:
& then another 10 sheets with the vehicle callsign.
So
Sheet 1 :- Input all data sheet
Sheet 2 :- Y4
Sheet 3 :- Y7
& so on.
I need a formulae that will copy multiple info to the relevant sheets, I have found a way of copying multiple info, with the formulae below, but I can't get it to work if there is more than 1 sheet.
=IF(ISERROR(INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2)),"",INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2))
View 9 Replies
View Related
Jun 18, 2013
I would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.
The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.
View 6 Replies
View Related
Jan 5, 2007
Im trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
View 9 Replies
View Related
Sep 11, 2009
I'm having difficulty creating a nested "if" or range formula that basically only reads the first two characters of the data in a given cell. See attached spreadsheet.
In column "A" I have listed the product ID. If the product ID has the first 2 characters 'DV' then it should yield the result of Dryer. The main issue is creating a formula that only picks up on the first two characters.
View 4 Replies
View Related
Jun 5, 2009
picks up the drifferences between low and high numbers for example 1-10 is low 11-29 high and 0 is a alternate so say i start typing in 1,2,3,11,15 in the top cell they start decending down the rows automtically and the program has 2 boxs indicating 3 lows and 2 highs and wen i type in a 0 it resets the two boxs to 0
View 5 Replies
View Related
Jul 10, 2009
I have two workbooks kept open - ABC and 123.
I need a macro or a formula which picks up the workbook name - ABC
Simply I will always run a macro in the workbook 123. So i should get the workbook ABC automatically
View 8 Replies
View Related
Nov 14, 2009
I want to be able to define on excel 2007 what gets cut when I cut and paste
cause I just want text, comments, and font formatting, i dont want the fill of the background or border when I cut and paste
View 9 Replies
View Related
Aug 11, 2007
if I use the following formulae in A1 and fill it down, I get the serialization 1,2,3....etc. =(INT((ROW()-1)/1)+1)*1
But if I begin formulae at A11 and fill down, I get 11,12,13.....etc. Now I type 1 in A11, how do I modify the formula so that A12 will be 2, A13 will be 3, A14 will be 4 and so on ?
View 4 Replies
View Related
Oct 29, 2008
At the moment all our work is logged by codes eg. A1Misc, B5Change, N9Important. As well as the codes, we enter the date in the column next to the code along with another Column to show the amount.
I need a Macro to look at the date column and only take dates which have Sept then go to the column next to it and pick up the codes individually and sum up all the amounts for the codes.
I have attached an example which shows from A13 downwards what I need the macro to show when finished.
View 3 Replies
View Related
Jan 10, 2008
The thing is that i have alot of sheets (more than 20) and my program picks 2 values from the same cell locations on each sheet. This leeds to more than 30 arguments in my formula. I am gonna use this for alot of excelfiles constructed in the same way, but with varying order and names on the sheets.
View 9 Replies
View Related
Sep 23, 2009
I am entering values in columns AV and BD of the attached spreadsheet. Rather than manually editing each value to adopt the decimal place value of the adjacent cell is there a formula that can achieve this before the value is entered? The decimal place values are determined using a VLOOKUP table (column3) on the NES tab.
View 2 Replies
View Related
Jan 21, 2006
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
View 9 Replies
View Related
Mar 14, 2003
I want to pick the last number entered in column and put it in a formula. Is there an easy way to do this, or do I have to stack a lot of IFs in a string.
View 8 Replies
View Related
Dec 6, 2007
I have a group of validation list boxes, all with the same choices, that when a user selects a certain item from them I want a popup box with extra info in it to appear. Is it possible for the user to click on the popup box once to make it disappear again?
View 6 Replies
View Related
Mar 25, 2014
I think those are what I need but I can not get it to work.
What i would like is:
In column E rows 2-12 I will be entering one each of the items listed below in column e rows 14-23. As I enter an item in section (e2-e12) the corresponding entry in section (e14-e23) disappear leaving me with only the choices that have not yet been used.
View 6 Replies
View Related
Jan 6, 2004
Is it possible to use a vlookup to produce a number of results…
I have a list of surnames, and I want to use the vlookup to find all the Smiths in the list, obviously a basic vlookup only brings back the first Smith it finds….
=vlookup(A1, download,1,false) , where A1 is some sort of search box for users
or is there a simple VB solution?
View 9 Replies
View Related
Feb 4, 2010
I am trying to simply count the number of times each entered name appears on my list IE if John Smith appears 3 times in one sheet, in a column after his name would simply be the number 3. I tried this doing =COUNTIF(A8,A:A) Where A8 is his name and column A is all names. I keep a return value of 0 every time!!!!! I even tried =COUNTIF(A7,A12) where they were both the same names. And yes,I did do Ctrl + Shift - enter
View 2 Replies
View Related
Jul 9, 2014
trying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
View 8 Replies
View Related
Mar 21, 2008
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
View 9 Replies
View Related
Jun 17, 2008
My colleague in accounts has a sheet that lists all the invoices and values and other 'accounts department things' that I know little about.
Anyway, she has to manually input all the invoices she receives onto this excel document but sometime receives duplicate invoices (& inputs them twice be accident).
Is it possible for someone with macro skills to create a macro to run in a worksheet so that if she enters a number in a given column more than once an alert box appears to tell her. I've summarised the 'rules' below*
*Can is be written so that I can edit which column the alert is based on?
[In (for example) column F is a list of invoice numbers - all unique]
If user types a number in column F that matches a number that already exists, show alert box "THERE APPEARS TO BE A DUPLICATE ENTRY IN COLUMN F"
====
Any further clarification, let me know i'll try to post a blank sheet if my boss lets me.
View 9 Replies
View Related
May 10, 2007
Example;
Cell 1 equals a number
Cell 2 is the number entered into cell one times(X) 35 up until the number 6 is entered. Any number above 6 is Times 50. So. 1-6 is times(X) 35 while 6+ is Times(X) 50.
I am. Here's another example:
A salesperson gets a commission of $35 for each widget sold up to the first 6 widgets. If he sells more than six widgets, he gets paid $35 for the first 6 and $50 for anthing over 6.
The sales person will enter the number of widgets sold into a cell and the formula should do the rest.
I have been stuck on this for months.
View 4 Replies
View Related
Jun 3, 2008
I've done lots of looking on the forum, and the helpfiles, but just cant seem to format my text box properly! Its a form where the user inputs to the text box. I want to control thatway where they enter numbers e.g 05052008 and it gets converted to 05/05/2008 or 05-05-2008. I've tried a range of things, but am really stumped!
View 2 Replies
View Related
Sep 10, 2013
I have attached sample.xlsx...In this spread sheet, two sheet are there..
1. Invoice Level
2. Product Level.
Both sheets Billed amount, discount amount & After reprice amt should match....Invoice level sheet - inv# is unique...but in product level sheet have inv# is billed like product level sale and discount amount also distributed product wise....
Here the the billed amount and discount amounts are not matching with 2 sheets. How to find the which inv# product was billed with wrong price or discount...
View 5 Replies
View Related
Jan 15, 2014
I created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
View 3 Replies
View Related
