Formula To Return The Value In A Column Based On Cell Entry In Row
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Comment Cell When Row With Consecutive Column Has Same Entry
Need a formula to automatically comment a cell when rows with given number consecutive columns has same entry example: A B C D E F G H 1 0 0 0 0 5 6 7 18 2 0 0 5 6 7 0 8 26 3 0 5 0 0 0 0 0 5 row 1: there are minimum 4 '0' in consecutive columns, so H1 is sum of A1:G1 = 18 and automatically comment cell H1 "there are 4 zero" row 2: because there are no 4 '0' in consecutive columns, H2 is 26 , and no comment row 3: there are 5 '0' in consecutive columns, so H3 is 5 and automatically commented cell H3 "there are 5 zero"
View Replies!
View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following: Week1 | yes Week2 | no Week3 | no Week4 | yes Week5 | no ... Week28 | yes I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.
View Replies!
View Related
Insert Formula Based On Cell Entry Using Vba
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3 i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
Formula To Return Last But One Cell In Row
I currently use the following formula to find the last used cell in a row: =LOOKUP(2,1/($6:$6<>""),$6:$6) which works fine but I have been trying to amend it so that it returns the last but one cell in a row. Have tried using it with Offset but without success. Have found other solutions to finding to the last cell in a row albeit that a number of them do not seem to work with my project and likewise none of them seemed to allow customisation of any sort.
View Replies!
View Related
Formula Return The Address Of The Last Numerical Entry
It's a spreadsheet of about 300 rows, each row corresponding to a certain department. each row is filled with various data, both text and numerical, including various different scores and averages. the columns include daily scores and weekly averages. I want to be able to create a new column that will calculate the time (in days or weeks) since the last score/average was entered. 1. a formula that will return the address of the last numerical entry in a given row. (I've got a MATCH formula that will do this, although it doesn't let me specify different cells or ranges, only the entire row) 2. whichever cell is returned will give the date instead of the value. (for example, if the last value entered is in $F$63, and Column F is headed 23/11/09, then the formula will give the date) 3. then finally, calculate the time between that date and the present time I've tried various different things, both in normal Excel and the VBA editor, and I'm beginning to think maybe this isn't even possible.
View Replies!
View Related
Formula: Return Result Based On Other Cell Values
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this: if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is -1% then b6 =96 if B5 is -2% then b6 =93 if B5 is -3% then b6 =90 if B5 is >-3.01% then b6 ="Market Test Required" So on and so on.... I would really appreciate your help on this issue. I have used excel for some time now but not with complex formulas or any vb.
View Replies!
View Related
Return Formula Value/Text Based On Many Cell Conditions
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses) Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses Is there a formula/function that i can use (like the IF Formula) to complete the following information; EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO" Column H lists all departments Column A lists deaprtments A3 representing the 1st Department needing training
View Replies!
View Related
VBA Code To Go Through Column Cells & Return Value Based On Cell Value
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying: Sub usstates() Dim R As Range, s As String Set R = Sheets(1).Range("J1:J50000") For i = 5 To R.Rows.Count X = UCase(R.Cells(10, 1)) If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then R.Cells(i, 2) = "SC" End If Next i End Sub.................
View Replies!
View Related
Find Data In Column Range & Return Cell Same Row But X Columns To Right
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different. The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work. What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
View Replies!
View Related
Find Cell Based On Column And Row
I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z So for example B would be P1W1 and C would be P2W2, etc what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ? Apart from pivot table.
View Replies!
View Related
Lock Cells Based On Entry In Other Column
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
View Replies!
View Related
Find Row Number Of Last Non-blank Entry In A Column
I want to find the Row Number, not necessarily the cell value, of the last non-blank entry in a Column. If the address of this row is found, then that could also be useful. I believe there are some simple Excel functions to do this, maybe involving the X1Up feature. I've searched the threads and haven't found a clear answer this. Below is code that I custom wrote, but its long and tiresome to use. 'The purpose of this sub is to find the last filled row in a column 'Knowing this last row is useful for telling later ' looping operations which row to stop on. 'The logic of this sub is that it will look down a column.........
View Replies!
View Related
How To Lookup Row # Based On Content Of Another Column's Cell
I have a 3 column table with 20 rows. The first two columns contain travel expense data. The cells in the third column are blank exept for whichever cell I enter an "x" in. Please let me what formula or worksheet function do I use to calculate the travel expenses based on the data in the first two columns that correspond to the row of the cell with an "x" in the third column?
View Replies!
View Related
Copy Information Between Worksheets Based On Specific Column Entry
My workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this? Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
View Replies!
View Related
Macro To Delete An Entire Row If A Duplicate Entry Appears Only In A Certain Column.
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column. 1. Look for the column header with the name "File Number" 2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column. This is regardless of what is contained in the other columns. For example..let's say these cells contained this data... B1 - UTE00225 B2 - UTE00546 B3 - UTE65513 B4 - UTE00225 B5 - UTE00225 In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
View Replies!
View Related
Cell/Range Names Based On Row & Column Intersection
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel]. I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
View Replies!
View Related
Copy Formula Down Based On Last Cell In Another Column
i want to insert value from the name of file from this formula "=MID(CELL(""filename""), FIND(""["",CELL(""filename""))+1,10)" to range b2 until last empty range that i found form this formula =ROW(OFFSET(A1, COUNTA(A:A)-1,0)) Sub lumu() Dim x As Variant x = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))" Columns("B:B").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("B1").Select ActiveCell.FormulaR1C1 = "kode_wilayah" Range("B2").Select ActiveCell.FormulaR1C1 = _ "=MID(CELL(""filename""),FIND(""["",CELL(""filename""))+1,10)" Range("B2").Select Selection.AutoFill Destination:=Range("B2", Range("B" & x)), Type:=xlFillDefault 'this an error Range("B2:B123").Select On Error Resume Next ActiveSheet.Name = "t" End Sub
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
Return Column Letter Based On The Letter In A Cell.
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column). When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier. =IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
View Replies!
View Related
Macro To Comment On Cells Based On Formula's (duplicate, Length, No Entry)
making a macro that will scan for duplicates, length & empty cells. I have the sample file below that does conditional formatting but it doesnt help as much because I want to show the reason for the highlight's on a comment instead. sample file : [url] file that might help out: [url] checks would be: column A - duplicates and/or length should not be over 100 characters column B - duplicates column F - should only contain 2 comma's(or 3 keywords) all columns - check if no entries are found(empty cells)
View Replies!
View Related
Cell Sound Based On Entry
I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong. This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....
View Replies!
View Related
Countif - Based On Other Cell Entry
dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'. =COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")
View Replies!
View Related
Return Uniqe From Column B Based Column A
i tried to search on forum abou unique function but i didnt find what i need. I have 2 columns - column A - is "ID" and column B is "name". I want results in column C. I need formula, which look at ID and give me in C in the same row the unique „name“ from B. If is the name by this ID first time in column, it return the name. If it second or more, it return blank.
View Replies!
View Related
Fill Value In A Cell Based On User Entry
I have a dilemma. 1.I want to be able to a have a cell be filled with a certain value when the user enter data in Column C. 2.I know that this can be accomplished by a formula, but my spreadsheet will work my efficiently with a macro. 3.I do not know much about VBA but I believe that I will need a macro for each sheet that fills in the word.
View Replies!
View Related
Force Cell Entry Based On A Condition
I need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View Replies!
View Related
Restrict Cell Entry To Numbers Based On Another Cell
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple: Cell A1, they can select Rice, Cheese, or Rabbit I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10. Can I do that with data validation? I can't get any if thens to work in it.
View Replies!
View Related
Copy Data Based On Cell Entry To Different Spreadsheet
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid) example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account. If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1. If D:D is "payment pending" do same as above but in row 2 and so on.
View Replies!
View Related
Lookup Value Then Return Value In Row 1 Of That Column
I have a list of values in column A (part numbers) In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc). In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2. I've been trying to do this for a while but am making no progress at all.
View Replies!
View Related
An Array And Return The Same Row In Another Column
I have an array with a lot of rows and columns filled with different codes. I need to look into this array for one specific number and return the value in the same row in an specific column. I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.
View Replies!
View Related
Return Row# Of Max Value In A Column
I need (index? vlookup? if? match?). I did try this on my own and searched the board for help, but I couldn't seem to find something that applied. Sorry if this question is too simple or has been asked before. In Excel 2003 I want to return the row # of the maximum value in a columnar range.
View Replies!
View Related
Auto Return To Column A But Of Next Row
Is there an easy way to auto return to column A but of next row. I would like to be able to do this automatically say when column 87 is reached. I could just arrow down and hit home but there must be a way to automate this. I am currently in 2003 but will be moving to 2007 in a few months.
View Replies!
View Related
Restrict Cell Entry Based On Another Cell
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell. So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
View Replies!
View Related
Find & Return Value Same Row, Different Column
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row. I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?
View Replies!
View Related
|
TRACKER
