Mixing If And Vlookup Functions.
May 1, 2007
I have made an input form which the user can input the data of person they wish to sit.
I then used Vlookup to put this data on to a smaller table.
This will make more sense on this download of the program.
http://files-upload.com/199899/MainInput.xls.html
What my problem is....is that i have too rooms and i needed excel too look at the cell see that they want to sit in room one then it looks at what table they want to sit at then looks at the seat they want to sit at then put them there.
View 9 Replies
ADVERTISEMENT
Aug 3, 2009
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies
View Related
Aug 25, 2007
I'm trying to write a macro that looks up Column A so that each time it finds "Agent" as the first word in a cell, it takes that whole cell and copies it to another sheet.
I tried creating an array function that combines the If, Vlookup, and Right() and Left() functions, but no luck. Ideally, I would want this as a VBA macro.
View 9 Replies
View Related
Sep 30, 2009
IN NEED HELP WITH vlookup FUNCTIONS:
1-st table
ABCDEF12221.985
2-nd table
>40
0.1
40
View 9 Replies
View Related
Sep 20, 2007
I have a spreadsheet with over 20,000 rows. From another spreadsheet I need to find if any of a list of 90 customers are within the first, using a simple IF and vlookup formula which comes back yes or no.
I am using: =IF(A2 = VLOOKUP(A2,ihcust!B:B,1),"YES","NO")
However, although this works when they are spelt exact in both sheets if they are not it will say no.
eg. If I was Looking up "Joe's Icecreams" from the list of 90 but in the other sheet it is listed as "Joe's Icecreams (admin)" it would not recognise it and come back no.
Is there away to get around this problem
View 9 Replies
View Related
Nov 27, 2009
need a mix of the vlookup and sumifs functions! (well I think at least!)
I want to find Cell A in Column 1 AND Cell B in Column 2 (like a sumifs) and then return me a value in a range (as a normal vlookup would)
Basically a vlookup matching two cells/columns rather than one
View 9 Replies
View Related
Apr 7, 2014
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).
View 7 Replies
View Related
Jul 13, 2006
I have drop down fields using a vlookup to grab the corresponding data in a colums next it which is being concatenated into a one big cell. The problem is a set of other columns that I need to pull data from but its dependant to a previous column. In the attachment you see which ever region is selected the following column data is grabbed and the same goe for title. Now when the location is selected the info in its column should be selected that corresponds to the row that the specific title is on. example
if selected: North America>Secretary>Texas
results: A Crazy Mix->;typical,Notepad, pen,square dance
View 7 Replies
View Related
Jul 18, 2007
I am trying to obtain price of an item using vlookup() function on the identifier “0003128” (7 digits) stored with “custom” format “0000000” in one worksheet. However in other worksheet, the identifier is stored as “000312” (6 digits) with “text” format without the last digit ‘8’. I tried using the left(A,6) function on “0003128” but instead of returning “000312” it returns “3128” and I’m unable to use the vlookup() function.
View 6 Replies
View Related
Oct 2, 2008
XL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
View 9 Replies
View Related
Jul 16, 2009
when combining two columns using C1=A1&B1 formula, the column C only works until the columns A and B exist; and the column C does not work when deleting A or B.
View 5 Replies
View Related
Dec 30, 2009
I've got an Excel document I've built which uses multiple ODBC QueryTables
View 3 Replies
View Related
Jan 13, 2014
I'm working on doing some data analysis on an excel workbook that comes from a tracking website's reporting system. The data is inconsistently merging some cells in rows where there are multiple entries in a column, and on other cases is not merging those cells.
The format of the spreadsheet is as follows:
A
B
C
1
Account #
Current Services
Order Status
2
1234
PACK1
Completed
[Code] ....
In this example, cells A3:A5 have been merged by the reporting system's export function, but B3:B5 were exported as individual cells. On the other hand, cells A6:A12 are not merged into a single cell.
My goal is to look at the values in column B for each account and compare them to an array of service package codes and to see if any values in B match value in that array for each account number. If the system of cell merging was being consistently applied across all cells, I could figure out a way to link the results for each group of cells in column B to the account number they match up to, but since there are some account numbers that are in unmerged cells, I'm not sure what to do.
I can't alter the way the reporting system exports its data to Excel format, so how to deal with a mixture of merged and unmerged cells in column A. How I could do this without a lot of manual adjustments?
View 3 Replies
View Related
Jul 29, 2009
I am very comfortable with using sumif statements although I have tried it link two data from 2 different sheets and I keep getting returned #VALUE!
I get a message "Unable To Read File" in a pop up box before fully opening.
View 9 Replies
View Related
May 20, 2013
This works
ws2.Range("C5").Formula = "=sum(D5:ZZ5)"
guide me on the syntax when the final column reference is a variable
ws2.Range("C5").Formula = "=sum(D5:" & Cells(5, l_LastCol) & ")"
doesn't work
View 3 Replies
View Related
Mar 14, 2008
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
View 9 Replies
View Related
Dec 4, 2009
I want to calculate the following...
If Cell A>40, Then the result should be ((Cell A-40)*23.25)
If it is not >40, Return 0.00
This is for a timesheet / overtime.
View 2 Replies
View Related
Jan 12, 2010
Is it possible to use excel functions with in VB? (such as CONCATENATE, LOGEST etc.) For example, if I have a site no. and data for this site in an array could I append the site number to the data by:
View 4 Replies
View Related
Apr 19, 2007
i have a spreadsheet acting as a type of database (our work wont pay for more licences for access!), and it has multiple functions running. well, it has 3000 lines x2 for 2 sheets with about 5-10 full colums of functions. this means the template file i have starts off at about 10mg. with data, this goes upto around 15-20mb. howver, we get a lot of the massive size increases. however, the sizes we get are often upto 150mb! so, i dont know vb at all, and have only learned functions recently. was hoping someone might be able to help out sorting this out? i dont know if i can upload a coppy online, but i can email.
View 7 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Jun 9, 2014
So I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.
To visualize, i just want to add up the students that have not attended a class.
View 14 Replies
View Related
Jun 19, 2014
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type
Sample file attached.
View 14 Replies
View Related
Jun 30, 2014
I am trying to write a formula that will satisfy the following:
C1 and G1 are number values.
IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.
View 2 Replies
View Related
Jan 8, 2009
I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.
View 4 Replies
View Related
Nov 23, 2009
Currently, all of our data in Excel are linked by using Excel Functions between workbooks and some of the files are so large that it may take 10 minutes to open and copy and paste a cell may take three minutes (which is ridiculous slow).
Pardon my ignorance, my understand of Excel VBA/Marco is about automation. Even i use marco to run the excel, i still need formula in the cell in order to perform the task, right?
View 5 Replies
View Related
Jan 15, 2007
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
= left(worksheet name! row(1) column.range("startno"),2
View 10 Replies
View Related
May 18, 2009
I have a cell (D19) that I want to be automated to return a value based on a formula (it will equal Cell D15). But I also want to have the ability to allow the user to override this and enter a manual value if needed. My idea on how to do this is having a check box with a macro where if it is checked it will unlock the cell and clear out the formula and when checked it will reenter the formula and lock the cell.
Also same form - I need a check box that will check all applicable check boxes (all boxes under Deductions to be taken). Can anyone assist me in setting up these check boxes so that my functions will work, or if there is a "better" way of providing these functions please let me know.
View 5 Replies
View Related
Dec 3, 2005
The file I am working with is available here
(1) I need to work out the value of 2 years worth of interest on a loan - details would be
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months
I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ...
the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same)
The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ...
(2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ...
what I would like to be able to do is
(a) insert the actual payment dates
(b) if no payment is recorded for that period, automatically insert an overdue fee
View 14 Replies
View Related
Jan 25, 2007
There are three situations that should yield the answer "overdue" and a fourth and fifth situation that should yield an answer "on target". I'm new to functions and have spent SEVERAL hours on this
{=IF((G4<>0,F4<$g$1="Overdue")+(F4<G4="Overdue")+(G4<>0,F4>$g$1="On Target")+(F4>=G4="On Target"))}
View 10 Replies
View Related
Oct 3, 2008
I'm trying to find a function for the following statement:
=number of rows where columnA="value" AND columnB<ColumnC
So this formula would return 2 from the below table if the "value"=5...
X A B C
1 5 5 6
2 5 8 9
3 1 6 7
4 5 9 8
5 5 9 9
View 2 Replies
View Related
