I have a database with over 100 products listed across the first row.
Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
For Example
A B C D E etc.
Products --> X Y Z AA
Proj 1 2 3
Proj 2 1 4 5
Proj 3 2 4
etc.
I want to be able to create a report for any given product.
The report could look like,
Product Z
Proj 1 3
Proj 3 2
So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.
I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.
i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code
VB: Private Sub CommandButton1_Click()Dim k As Variant Dim b As Variant Range(Me.RefEdit1).Name = "item"
My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...
In N42:N72 there is a corresponding $ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the $$ amount to the number 5 in column H42:H72)
I've tried using INDEX MATCH but can't figure this out.
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.
I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
In D8, the value is Blue. Here is an example with made-up values (X is meaningless values) --
A B C D E F G X Apple X X X X Blue X Pie X X X X Blue
When I use the function, I want it to retrieve Pie, but it always retrieves Apple. Is there a way to ignore Apple? I've tried throwing "Apple" in there a couple different ways, but nothing is working. Or, if I can search from the bottom up, that would work as well.
I am working with a group of individuals that will be passing around an excel spreadsheet to one another, and wanted to come up with a way to have the 1st column act as an index, with the key component requiring that the index column would automatically re-number itself, if someone entered a new row.
A typical value in the first column looks like this: 8-5-012-005
Which in our case means that there are 4 series of number sets, separated by dashes. So the first set is 8, the second 5, the third 012, and the fourth 005. The available range for the sets are 7 or 8 for the first, 5 through 9 for the second, and 0 through 130, and 0 through 200, respectively.
So the user can pick any of these ranges for when they decide to create a new record (row).
Here is the way the spreadsheet columns currently look (always sorted by Tract_ID):
Now, here is the way I'd like to have the spreadsheet columns look with the Index_No (can be either Numeric or Text - depending on your recomendations). The sort order is based on 1st, the index number, then 2nd the Tract_ID:
Then, let's say the user wants to enter a new value like say, 7-5-105-021. That value would need to go between Row-1 and Row-2, which, if they just inserted the value in the row of their choice, would screw up the indexing.
What I need is a way to ALWAYS create an index (automatically), no matter where they decide to put the value in the spreadsheet, AND it would update all of the other Indexes as well (very important requirement).
Following is a summarized example of my data and what I am trying to accomplish.
[Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D].
My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI.
This is simuilar to the Column Index Number used in a VLOOKUP formula.
I've written this code to find a value in the worksheet chosen from the combo box (I think it works as its doesnt error), but I now need too write some code to pull out the row number from my code and put data into the same row (but different columns). This is my code so far:
Code: Private Sub BookButton_Click() Dim rFound As Range[code].....
I want to find the minimum value of column A if there is anything greater than zero in column B. I tried this formula (simplified): MINIF(B2:B6,">"&0,A2:A6) And it gives me the # NAME? error
I need a function to find the number of decimal places of a certain number (in this specific case doubles)
The first solution would be something like this:
Code: 'returns the number of decimal places within a double Public Function getDecPlaces(inputNum As Double) As Long Dim ndx As Long ndx = InStr(1, inputNum, ".") If ndx > 0 Then getDecPlaces = Len$(CStr(inputNum)) - ndx End If End Function
But i feel there is likely a much better way of doing this..
i m given 12 max value of my 292 cells. now im asked to find number of cells that falls within each of these max values?? im asked t use histogram. how will i do it?
to update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?
This is what I have done so far:
Dim myRows As Integer
With Sheets("Mrt")
'Retrieve history information for row For myRows = 4 To 49 If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then textboxbedrag.Text = Range("C" & myRows).Value chkBTW_Ja.Value = Range("D" & myRows).Value txtNota.Text = Range("S" & myRows).Value End If Next End With
Basically where the columns say 2011 or 2012 AND 1, 2, 3. I want to be able to have it index the number below based on the GL number on the left and both the year and period on the top. I think that you can do with using the sumproduct function with the binary, but the computer is a little dated and it takes a while to run those calculations.
I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"
This function searches in whole sheet,but i need only in C column! Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
I'm having trouble coming up with a formula that will find the first month with a number in the 'Total' row (row 22) without using an array function or VBA. The months are in row 13 and can vary from 1 to a whole lot. The number of line items, column C, will also vary, but I wouldn't expect that to effect any function (aside from changing the row designation). Here's what I put together using an array function: {=MATCH(TRUE,ISNUMBER(D22:L22),0)}
Again, don't want to use an array function. Other people will be using this and when they mess with it, i don't want them asking me why it won't work anymore.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutM14M15M16M17M18M19M20M21D22E22F22G22H22I22J22K22L22M22= CDEFGHIJKLM12LineMonths*AROTot13123456789141*********0152**5*6*2**13163**4**3***71740000*****0185*421***5**30196***1***8*9207*4**9**3*16218*********022Total* 8301153711* 75Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have created a calculated column in PowerPivot and inserted a formula that worked in a normal excel spreadsheet however, this formula does not work in PP anymore.
=find("green",Table1[Name],1)
Name column: NAME: Bag green
it should find "green" in the name and return the position. Hoever it alway returns the error, that the find function could not find the string.I have attached an example spreadsheet.
The simple table (attached) represents our problem.
We want the letter in Column-B that corresponds to the last non-zero entry in Column-A. So in the example provided, the answer will be cell B12 which is "L". Also, if there are blanks (rather than zeros) below the last non-zero entry, it still needs to work.
I have a a spreadsheet with content (text) in one cell pr line. The content can be in the range column 1 to 8. I want to make a formula where I get the number of the column where there is content. I know how I can make this by using nested IF's, but are there any better proposal?
I would like to disregard the digits before the hyphen (not always four digits) and check only the values of the last set of digits (also not always 4 digits). I would like to search for the greatest suffix used to find the next available number. In my data above the result would be 132.
I Have a row witch contains some numbers…..some of them are zero. I want to look: column after column throw this row and find first not zero item. For example:
A B C D E F 0 0 0 12 3 0
Result is “D” BUT!
U want to make it with standard excel functions, without programming any user functions with VBA! Only Excel…
