I am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies.
I have a data set where a1 is the starting cell and it needs to equal cells b9 or b6 and if the cells in the a column match the data in b9 or b6 it should be assigned an 1 if the cell data in column a does not match the data in cells b9 or b6 it should be assigned a 0. I've tried multiple formulas and have not been able to find the right one
I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.
I need to add a Bloomberg BDP function to my lookup statement. The formula that i'm trying to replicate in VBA is =IF(ISBLANK(h23,"",BDP(H23&" cusip","long_comp_name")). update my macro.
I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows.
In the table above: - Code B in report 100 would go for a distance of 7 (12-5). - Code A in report 100 would go for a distance of 6 (16-10). - Code B in report 200 would go for a distance of 6 (10-4).
How the Lookup will break down within an IF statement?
Original
HTML Code: 'ActiveCell.FormulaR1C1 = _ "=IF(R[-1]C[-2]"""",LOOKUP(1000,SEARCH({""Principal"",""Foreclosure"",""Interest"",""Premium"",""Discount""},RC[-1]),{1,1,2,2,2}),"""")"
Non-Working If statement. I realize that the Lookup isn't currently here. Including it kind of defeats the purpost of breaking this out into IF's, doesn't it?
HTML Code: If MyFirstCell.Offset(0, -2) "" Then MyFirstCell.Offset(0, -1) = Search("Principal") MyFirstCell = 1 ElseIf MyFirstCell.Offset(0, -1) = Search("Foreclosure") Then MyFirstCell = 1
I want to lookup a value from cell C4 in 'workbook B' if the value in cell G3 of 'workbook A' equals "Yes". If the value of cell G3 in 'Workbook A' equals "No" I need the value to be 0.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
I am currently using an Intersect statement in a worksheet module to perform two things: 1. Insert a time stamp into row 2 when row 1 has a price inserted 2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user. If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.
how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.
I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.
I have a list of numbers from 1-7 would look like this each number in a seperate cell.
3 5 1 2 3 5 6 7 2 4 5 6 7 6 7 4 6 6
I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.
The combos that would work: row 1 use 3 ----------- row 1 use 5 row 2 use 1 ----------- row 2 use 1 row 3 use 2 ----------- row 3 use 2 row 4 use 7 ----------- row 4 use 7 row 5 use 4 ----------- row 5 use 4 row 6 use 6 ---------- row 6 use 6 5 would complete ---------- 3 would complete
Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.
on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).
Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2
I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.
I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:
1, 2, 3 1, 3, 2 2, 1, 3 2, 3, 1 3, 2, 1 3, 1, 2
The functions in Excel available only give the total number, but I want to see these combinations!
I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?
I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.
Function FirstCheck(Count1, Count2) Select Case FirstCheck Case Count1 = "", Count2 = "" FirstCheck = "" Case Else FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2)) End Select End Function
I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value.
The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected.
For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field: Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected. What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop". Note: The sheet will have password protection.
i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix.
I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the drop-down selection. nor does it add columns together.
I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.
I have to write a formula which states the following:
if cells AA1,AB1 &AC1 = 0 then "Slow-Moving", if of these cells AA1,AB1 &AC1 contains a number then "OK", if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"
I believe an If and AND combination could work but its not working for me.
write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.
[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]
