Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
- Find Date In Column & Return Adjacent Cell Value
- Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today
- Find Cell Value In Another Workbook & Return Adjacent Cell
- Find Text String And Adjacent Cell Contents
- Find Value In Column And Return Value From Adjacent Column
- Find Text Within Cells & Place Same Text In Adjacent Column
- Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
- Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell
- Find Value In Column & Return Cell On Same Row- Different Column
- Find Cell Value In Column & Return Corresponding Value
- Find Value, Copy Adjacent Cell & Paste To Corresponding Cell Of Another Found Value
- Cell Reference Or Adjacent Cell Return
- Userform Find Cell From ComboBox Value And Write To Adjacent Cell
- Find Cell, Copy Adjacent Cell Or Cells To Clipboard
- Find Cell Value Copy Cell And Adjacent Cell
- Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell
- Find Data In Column Range & Return Cell Same Row But X Columns To Right
- How To Return Value Adjacent To Cell From Another Sheet?
- Function To Return Value Of Adjacent Cell
- Return Value Of One Cell And The Adjacent Cell As Well
- Adjacent Cell After Find
- Change The Value In The Cell Adjacent To The Cell In Column B
- Lookup Nth Occurrence Of Date & Return Adjacent Cell
- Using The .find Method And Getting Adjacent Cell Data
- Find A PORTION Of Text In A D Column And Automatically Adds The Price To The Right Of It Together In One Cell
- Find Text/Value & Use Found Cell, Row, Column In Macro Code
- Find Unique Values, Transfer Adjacent Cell To Another Workbook
- Find Cells Matching Value & Return Adjacent Details
- Show Text If Adjacent Cell Is Not Blank
- Move Adjacent Cell Into Rows, Same Column
- Format Column Until Adjacent Cell Is Empty
- If Column A = 0 Then Adjacent Cell In Column B Divide By .3
- Module To Find & Move Based On Cell Adjacent's Set Value
- Search A Range To Enter Text In Adjacent Cell
- VBA If Cell Is Blank Import Value From Adjacent Column
- Gathering The Sum Of Cells Based On The Text In The Adjacent Cell
- Lookup Left Most Column & Return Adjacent Value
- Find Cell In Array, Return Value Of Cell Next To It
- Formula: If Value Return Is = To Value Of Cell Above Then Find Return Next Value
- Lookup & Return Values Of Adjacent Column To Textbox
- Create Multiple Text Files From Cells & Save As Name In Adjacent Cell
- Lookup Column Values On Another Sheet & Return Adjacent Cells
- Lookup/Match Column Values On Another Worksheet & Return Adjacent Cells
- Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
- Find Last Cell In Range, And Changing Another Cell Based On Offset Text
- Find All Instances Of Cell In Column & Check Offset Cell
- Find Part Of Text Within Cell And Replace From Another Cell
- Find 1st & Nth Occurence & Return Adjacent Value
- Formula Dependent On Text In Adjacent Column
- IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value
Find Date In Column & Return Adjacent Cell Value
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
View Replies!
View Related
Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values. I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008. CREATE TABLES LIKE BELOW?Column A Column B Column C March 1, 2008Saturday March 2, 2008Sunday March 3, 2008Monday March 4, 2008Tuesday March 5, 2008Wednesday ...................
View Replies!
View Related
Find Cell Value In Another Workbook & Return Adjacent Cell
I am trying to complete these steps: Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls" Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59" My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...
View Replies!
View Related
Find Value In Column And Return Value From Adjacent Column
I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively. What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).
View Replies!
View Related
Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives. After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero). Listbox and expected result are in attached example worksheet. The attached does show the need much better then I can explain it.
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
Find Cell Value In Column & Return Corresponding Value
I am drawing a complete blank of this one. I know it can be done but I can't seem to remember how I did this in the past. I have a sheet that has cells A1 at MB, A2 as ML A3 as MD Then in cells B1 I have MB,TL then in B2 I have ML and then B3 is Blank this time. Then in cells C1 I have Jim, C2 has Jack, and C3 has Fred. What I want to do is have a formula that is in cell D1 that looks at the value in A1 and finds that value in Column B and if it is found return the value in the same row in Column C. In the example it would show Jim since MB is found in cell B1.
View Replies!
View Related
Cell Reference Or Adjacent Cell Return
I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine! Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2. But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.
View Replies!
View Related
Userform Find Cell From ComboBox Value And Write To Adjacent Cell
I am having a problem using data from my combobox to find the cell on the worksheet and write to the adjacent cell. Worksheet name is Fleet ComboBox Name is ComboBox7 TextBox name is TextBox3 CommandButton1 I would like to be able to take the text from ComboBox7, find this text in Col A of worksheet Fleet and write the Value of TextBox3 in the adjacent cell in Col B. when the CommandButton1 is pressed.
View Replies!
View Related
Find Cell, Copy Adjacent Cell Or Cells To Clipboard
Here’s what I want to do in VBA 1. Put a value in cell H1 (text and numbers) 2. Find a matching value in column A (starting in row 2), error message if the is not a match. 3. Copy the adjacent cell column B (rows vary) to the clip board. It would copy until it found the first blank row. I have attached a scaled down version of the spreadsheet, the one I use has 100's of codes. I know some VBA but not much. I searched the forum but could not find anything.
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
How To Return Value Adjacent To Cell From Another Sheet?
I have one column of about 10 names (each of which is unique). The cell adjacent to these names is empty and I want to insert a value into this cell. The value I'd like to insert into this cell is located in another sheet attached to the same file. This sheet has the same layout, except the unique name has the value next to it and the order of the names is different. I'd like the formula to determine where the name from the first sheet matches the name from the second sheet. I'd like it to then display the data associated with that unique name in the first sheet, adjacent to the name in the blank cell.
View Replies!
View Related
Adjacent Cell After Find
I am trying to calculate an adjacent cell to place an interger value after a find result. The following example, lets say location=$L$5, I need to calculate Cells(13,5).Value =n. Its for a page number and the cell I need is always to the right of the found cell.
View Replies!
View Related
Lookup Nth Occurrence Of Date & Return Adjacent Cell
I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows: Function Nth_Occurrence(range_look As Range, find_it As String, _ occurrence As Long, offset_row As Long, offset_col As Long) Dim lCount As Long Dim rFound As Range Set rFound = range_look.Cells(1, 1) For lCount = 1 To occurrence Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole) Next lCount Nth_Occurrence = rFound.Offset(offset_row, offset_col) End Function The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1) Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
View Replies!
View Related
Find A PORTION Of Text In A D Column And Automatically Adds The Price To The Right Of It Together In One Cell
I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase. My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
View Replies!
View Related
Find Text/Value & Use Found Cell, Row, Column In Macro Code
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is Dim XColumn As Range ‘the cell containing the text “X” Cells.Select Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Set XColumn = ActiveCell I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
View Replies!
View Related
Find Unique Values, Transfer Adjacent Cell To Another Workbook
I have a main workbook that is meant to summarize data from other workbooks In Row 6 from column H on I have workbook names in each cell Column G in all workbooks (including the main one) contains our branch #'s for our offices For each workbook listed in row 6 , I need to open that workbook (I have that setup with the code below---notice there is an AX.xls that is appended to the file names listed in row 6 in order for the names to match what is in the windows directory) In the newly opened workbook, I need to, for each value in column G, copy the value from adjacent cell in column H (the dollar value) then search column G of the main workbook for a matching branch and paste the value to the appropriate row under the workbook name column (remember workbook names are in row 6) The trouble is, for each branch in column G in the newly opened workbook that cannot be found in the main workbook, I need to paste the new branch # at the bottom row of/in column G and the $ value (H column value from the newly opened workbook) to the corresponding row under the workbook name column Sub OpenWBs() Dim Rng As Range Dim WB As Workbook Dim MyPath As String Dim lastCol As Integer Dim newRange As Range lastCol = Cells(6, Columns.Count).End(xlToLeft).Column Set newRange = Range(Cells(6, 1), Cells(6, lastCol)) MyPath = "F:AccountingAPAdvertising AccountsLA TimesAgentExtractorCompletedLIST" For Each Rng In newRange '
View Replies!
View Related
Move Adjacent Cell Into Rows, Same Column
I need to create a repeatable macro/module to help me prepare a spreadsheet for import into Microsoft Access. In each row of the spreadsheet is various information including Order Number, Model, and Serial Number. The problem lies in the Serial Number column, which often contains several Serial Numbers delimited by commas. In order to serve my needs in Access, each Serial Number instance needs to become a new row ( record) wherein all of the information (except for the Serial Number) is the same. It is crucially important that all of the serial numbers are in the same column. (I tried using text to columns as a starting-off point, but seemed to dead end). Example: Order#1; Model1; Serial1, Serial2, Serial3 Needs to become: Order#1; Model1; Serial1 Order#1; Model1; Serial2 Order#1; Model1; Serial3
View Replies!
View Related
Format Column Until Adjacent Cell Is Empty
I am writing a macro that will format my table automatically. My table will start with 3 columns all containing data on a 1 to 1 relationship (I.e. there are no empty cells until the end of the file). Initially these are columns "A", "B", and "C" but they shift to columns "B", "C", and "D" during execution of this macro. However, my macro formats the table with seven addtional column headings which will eventually contain data though I need to format them prior to populating them. I know my column range is always going to be ("A:J") but my row range will vary on a weekly basis. That being the case, I need to know how to format the cells in these empty columns up to the number of rows containing data in the existing populated columns. For exapmle I would like the macro to format the table in the following way: 1. Insert new column "A" preserving the data in the existing column "A" shifting it to cloumn "B" and set Cell "A1"'s value = "#" --> I have already coded this portion. 2. Set Cell "A2"'s value = 1 --> I have already coded this portion. 3. Set Cells ("A1:J1")'s values = to desried column header title modifying the three pre-existing column headers while aligning the data to center --> I have already coded this portion. 4. Set all column headers to bold 10pt font and shade the headers row ("A1:J1")--> I have already coded this portion. 5. Set pre defined column widths for columns "A:J". --> I have already coded this portion.............
View Replies!
View Related
If Column A = 0 Then Adjacent Cell In Column B Divide By .3
Im wondering if this can be done in excel.. 2 columns if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30% this is the way I was trying to do this... =IF((O2:O6722)=0,DIVIDE(L2:L6722).3) I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
View Replies!
View Related
Module To Find & Move Based On Cell Adjacent's Set Value
ABCDE FGHIJK If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be: ABCDEG F HIJK
View Replies!
View Related
Search A Range To Enter Text In Adjacent Cell
When i press say a command button " Add Repair Information" i want the User to Enter the serial number which i have given it a range called SerialN from the excel sheet and enter a repair action. Is there a function in Excel which will allow me to search the Serial Number and allow me to put the Repair action across in the cell on the same line?
View Replies!
View Related
Lookup Left Most Column & Return Adjacent Value
Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be? Product Information RegionProduct IDProduct NameProduct Price Product IDHPPHomeware Pizza Pan19.95 Product NameSPPStone Pizza Pan 29.95 Product PriceHCDHomeware Casserole Dish19.95 Units SoldHCPHomeware Cookie Pan 9.95 Total SaleCWChina Wok 19.95 HEWHomeware Electric Wok29.95 WCMWilson Coffee Machine29.95 HBMHomeware Bread Machine49.95 HBMDHomeware Bread Machine -Deluxe89.95 HRHomeware Rotisserie119.95 OGGOlson Gas Grill 159.95 OEGOlson Electric Grill 159.95
View Replies!
View Related
Find Cell In Array, Return Value Of Cell Next To It
I have a table with multiple columns in which I want to find a value, then return the value that shows up to the right of it. For instance: I want to look for a unique value that exists in a cell somewhere in columns B-F, then I want to return the value of the cell in the next column. So if I have value XYZ, and it's found in row 12 of Column B, I want to return the value in row 12 of column C. Any way to do this with an index and match or some other formula?
View Replies!
View Related
Lookup & Return Values Of Adjacent Column To Textbox
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View Replies!
View Related
Lookup Column Values On Another Sheet & Return Adjacent Cells
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b. We have two worksheets, sheet a and sheet b compare sheet b, column c (email addresses) to sheet a, column x, (email addresses). If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View Replies!
View Related
Find Last Cell In Range, And Changing Another Cell Based On Offset Text
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F" and also t = time()
View Replies!
View Related
Find All Instances Of Cell In Column & Check Offset Cell
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks! the macro will look at the value I entered in cell A1, then find all instances of it in Column A in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
View Replies!
View Related
Find Part Of Text Within Cell And Replace From Another Cell
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2 The catch is that I then need to be ablt to change that character when I2 changes. Coloum C *VER *T’x’STATUS *ON *OFF *T’x’BANDS EG I2 - Variable = B *T’x’STATUS will become *T’B’STATUS I2 - Variable = A *T’x’STATUS will become *T’A’STATUS But " *OFF " should not change. I can change *T’x’STATUS to *T’B’STATUS but then I can change *T’B’STATUS to *T’A’STATUS Please see attached example file as it shows it better than I can explain it.
View Replies!
View Related
Formula Dependent On Text In Adjacent Column
I have a datatable which has a column containing up to 25 different text codes (which are currency pairs). I need to insert a specific number (as seen in the attached example) in a second column. This number depends on the text in the first column. I suspect this is very simple but at the moment, not for me!
View Replies!
View Related
|
TRACKER
