I am trying to use an IF Condition for multiple options and have forgotten how to make it work. Here is my basic formula:
=IF((B20="CHASE"),SUM(C20:I20))
I need to have several other options in B20 to "CHASE", like AMEX, DISC, BOA and CASH, so I need the B20 result to reflect which of those conditions exist in order to find the sum of C20:I20. I cannot seem to get any configuration to work.
i need to create an IF and an And formula for the debit columns and credit columns of an income statement and a balance sheet on a 10 column worksheet. The formula for each column will be slightly different but will be the same for every row in the respective column. the formulas must check to see if the account number is associated with an asset, liability, or owner's equity. this will determine if it is a credit or debit balance. my basic goal is to enter all values in unadjusted trial balance and adjustments, while all the other columns fill in automatically.
View 9 Replies View RelatedI've been playing around with the API functions (specifically, InternetOpenURL) to retrieve the contents of an HTTP webpage and store the information within a text file, using Excel VBA. It works great for any "normal" webpage not requiring a "Username" and "Password" login. I can't find any examples on how this is done for a webpage requiring such a login? I've heard you can do this via sending the appropriate cookie to the server, but I can't find any documentation on the web? Does anyone have any idea on how this functionality is achieved?
View 9 Replies View Relatedi have a graph that i've linked to let's say cells A1:C150
however, my range could sometimes needs to be shrunken to A1:A100 or A1:A40 etc. (the number of rows that are "active" is determined by cell K27)
i'd like for my graph to automatically adjust depending on the number of rows that are filled within the range above. can this be done with formulas and graph manipulations?
i've put in a formula currently that makes the value of the cells that would return 0 into "" but that doesn't seem to do the trick.
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))
If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works
If E8 contains a value that is negative then I want my formula to then display "Invalid"
If E8 contains the word "Trace" to return a value of 2
If E8 is blank then to return a value of 4
If the first character in E8 is ">" then return a value of 5
I need to look up a value from a worksheet using multiple criteria, and return MULTIPLE results.
I have a table with comments written by different people about specific items. I would like to be able to type in that item name and return all those comments. BUT - it's not JUST the item name, but also on a specific date or some other criterion.
Currently I have managed to do this successfully with a single criterion, as in this array formula:
=IF(ISERR(INDEX(F$1:F$1001,SMALL(IF(A$1:A$1001="ABC",ROW(F$1:F$1001)),ROW(1:1)),1)),"-",INDEX(F$1:F$1001,SMALL(IF(A$1:A$1001="ABC",ROW(F$1:F$1001)),ROW(1:1)),1))
I then copy that formula to a few more rows and I indeed get a list of different names (F1:F1001) which match my criterion "ABC". But - I have at least one more, or even two more criteria. I've tried a whole lot of different things and I just get errors.
Has anyone tried something like this? If you have - how should I modify the above formula to allow for additional criteria? For example, not only A1:A1001 equal to "ABC," but also B1:B1001 equal to "XYZ", and so on.
I want to be able to use list as filters that will show me results for prices I need. I have no idea how to figure this out. I attached a sample file so you can see what I'm trying to do.
View 4 Replies View RelatedI have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.
I need to produce monthly reports that list all the results for specific tests in a given month.
So, for example:
Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103
I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102
And the next month, make a report for April, then May... etc.
I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.
I would like to do a lookup function, in which multiple results are returned.
i.e.
VLookup Result 1
Result 2
Result 3
Etc.
I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.
I'm attempting to work with a v-lookup that will have multipe results. In column A of my spreadsheet are invoices numbers and in column B are account numbers. An invoice can have multiple accounts. For example, there are two lines for invoice "ABC", each with a different result in column B (see below)
Invoice Account
ABC Cash
ABC Receivable
My goal is to have the vlookup bring in both values, but in separate rows (see below)
Column A Column B Column C
ABC Cash Receivable
I'm trying to have an IF function analyze a sheet and then return a list of names that return a True
=IF(AND('LTV Watcher'!L2="Yes",'LTV Watcher'!M2=""),'LTV Watcher'!A2)
and it returns:
John Doe, LLC
however I'd like it to function to analyze a range of cells:
say L2:99, M2:99, and A2:99
After a lot of searching and trying I still didn't get to something that works.
My situation:
sheet1
column A column B
true "abc"
true "bcd"
false "cde"
true "def"
false "efg"
false "fgh"
on sheet2, in cell A1, I would like to get all results from column B that correspond 'TRUE' in that row.
So, in cell A1 on sheet2, i would like to see "abc", "bcd", "def".
VLOOKUP returns only one result, of course. How do I solve this?
I'm not good at VB, so preferably only with formulas!
Sheet1,
On this sheet I record the workers names and the time spent on one day, Each employee has a different rate and could do different hours, and also could come to this sheet twice. this sheet in the end give me, how much money I spent with each employee. Each employee is identified with a Number.
Sheet2,
On this one I record all downtime. Downtime's are identified with a Letter.
Sheet3,
Here I record the product's that we've done on that day, each complete product or part of a product is recorded in one row with quantity, elapsed time, product code (also a letter), in the end I know exactly how much time I used to do that product, and how many products from a specific code.
What I want is on the Sheet1, know how much time each employee and how many used to do "x" product. like this I can get the cost for each product and not only a total.
I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies View RelatedI have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx
I am trying to do a two way lookup with multlple results. In the example attached I want to know the names of the people who were in Boston on 01/02/09. I have tried a number of index, match formulas to no avail.....
View 3 Replies View RelatedI have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.
View 7 Replies View Relatedi have a workbook with 30+ sheets and i want to create a new sheet containing a table from aprox 6 values from the others.
i have done this in the past by typing =(then select the cell from the sheet i want)
while this works it is very time consuming and filling down after i have done 1 row gives the wrong results
I have a lookup that needs to average multiple results for the lookup... columns
Q to W
Looks like it is only returning the first found value.
i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.
that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.
i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.
in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.
in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".
there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.
I have some data arranged in columns/rows as follows:
Location Name
--------- ------
United States Sarah Buchannan
United States Walter Smith
France Phil Barney
Italy Anna Wilson
Germany Philip Watson
France Neil Anderson
I want to have some function in my spreadsheet that will neatly present the names of the people at a given location in a separate part of the worksheet
e.g. a display of all people in France would have something like:
France
-------
Phil Barney
Neil Anderson
Ideally I would like to populate cells rows in a different column with the multiple results of the lookup ("persons at a given location") and that change in location for any individual would result in the list of persons at a given location being automatically updated.
Did alot of digging around and managed to construct a UDF that would display the results of the lookup in a single cell - it is however not very readable. But, I found out that a cell based UDF is not allowed to populate other cells other that the one where the function is entered!
I would like a formula to do a lookup multiple results but in a sorted matter.
Example: ....
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
View 3 Replies View RelatedI am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
I am looking for a formula that would lookup multiple values/result, corresponding to one given value. I have attached a spreadsheet.
Column A and Column B consists of data set.
Column J has the "LOOKUP VALUE"
Column K is where we want the lookup results. We would require the result to be in ascending order which would determine the distribution.
Example1.xlsx
Is it possible to combine search results into a single cell?
On my spreadsheet I'd like to select from 4 columns and combine the result into 1 cell. I'd like to search on equipment number in column K that meets a condithion in column H, if this is a match I'd like to show the result date in column P and the duration in column R. Columns P & R shown in 1 cell...
Sort of like this... if "K" = "CV537" and "H" = "0" then show the resulting date from "N" and duration from "R" in one cell somewhere.
Possible?