Find Cell Value In Another Workbook & Return Adjacent Cell
Aug 12, 2008
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 3 Replies
ADVERTISEMENT
Jun 10, 2008
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 8 Replies
View Related
Mar 22, 2008
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 3 Replies
View Related
Apr 4, 2008
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 4 Replies
View Related
May 22, 2007
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 9 Replies
View Related
Jan 27, 2010
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 2 Replies
View Related
Jun 14, 2007
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 4 Replies
View Related
Apr 5, 2008
I have found many posts similar to what I am trying to accomplish, but nothing that I have been able to modify and make work. I need a VBA script that will find a cell with the text data "Difference" and copy the adjacent (to the right) cell's data. I then need to find a cell with text data "Ops" and paste the previous data to it's adjacent cell.
View 3 Replies
View Related
Apr 5, 2008
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 9 Replies
View Related
Aug 1, 2007
i am trying to find a cell in column C (if cell =140) and copy that cell and the adjacent cell in column "D" to worksheets called" upload" then repeat through multiple tabs- repeating process and dumping values in columns on the "upoload" worksheet
View 3 Replies
View Related
May 18, 2009
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 6 Replies
View Related
Sep 18, 2008
I am trying to write a function that will return the value in cell B based on its position adjacent to cell A (above). Cell A contains its own formula and may the values may change.
View 9 Replies
View Related
May 2, 2014
column 1 is vouchers
column 2 is date
vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.
I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.
View 1 Replies
View Related
Mar 18, 2009
I have some VBA which I am using to search though a worksheet for data matching an input box. I would like the search to produce a messagebox with the imputbox search result, as well as the adjacent cell. does anyone know an easy way of doing this? Below is the code I have so far:
View 2 Replies
View Related
Feb 25, 2014
A workbook containing 2 sheets. Sheet1 is a data entry sheet containing 8 columns and many 10s of rows of client data. The columns are (in this order) Client, Date, Code, Duration, CaseNo, Description, Staff. I also have a second sheet. Sheet2 takes the data from Sheet1 and displays the results of certain calculations, such as amt of cases a particular Staff person has and of what Code type.
Example:
- Total amt of P02 cases = 25
- # of cases Homer Simpson has = 10
So here's what I'm trying to accomplish:
Is it possible to have Excel return the total amt of time spent on a particular Code type? It would have to search through the Code column for a particular Code type (let's say "P03") and return the SUM of the Duration which resides in a different column.
Calculate by Billing Code.xlsx
View 7 Replies
View Related
Jun 5, 2008
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 2 Replies
View Related
Oct 21, 2008
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 2 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Apr 18, 2013
I have a phone list and I am trying to add a search box to it that when you type in the Employee name, it will return the value of the cell adjacent to the search result so the extension can show up.
I am a VBA Novice, but I have found plenty of code for the searching aspect of it. I am having trouble figuring out how to have the search occur and then return a different value then what was originally searched for.
Ideally I would like the msg box to show both the employee's full name and extension.
View 4 Replies
View Related
Mar 14, 2008
I was wondering if you have similar values how you use formulae to return the first or the last value from a list?
I have attached an example and I was wondering if you guys could have a look at it?
View 3 Replies
View Related
May 15, 2009
Until now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):
View 3 Replies
View Related
Feb 4, 2014
Is there a formula that can find a keyword within a cell then replace it with a formula that will add the adjacent cell value up.
A1=103 B1=104
A2=104 B2=105
A3=105 B3=Out-1p-(1)
A4=110 B4=111
A5=111 B5=112
A6=112 B6=113
A7=113 B7=114
A8=114 B8=Out-3p-(23)
I am looking for a formula that can automatically calc (B3) to 106 (based upon cell A3) if the cell has a value of (*out*).
View 1 Replies
View Related
Feb 21, 2008
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
View 5 Replies
View Related
Apr 3, 2014
I have one sheet in my workbook called 'mapping' which has a list of codes on it, the length of which will vary on a periodic basis.
I have another fairly large sheet on the workbook called 'data' (around 2000 rows) that will also vary in size. I want to do a search on the data tab for each code that is contained on column 4 of the mapping tab and if the code is found, enters the corresponding value from column 1 of the mapping tab to the cell 6 columns to the left of where the code was found on the data tab (cols H and B in this case).
The issue I have is the codes maybe contained more than once on the data tab so I need the find/replace command to search the whole of the data tab and perform the task each time.
View 2 Replies
View Related
Aug 10, 2012
I am using a macro to find a value in a cell and if found, replace the value in the cell to the left. Thr macro works fine - unless the value I am searching for does not exist. The answer is ( Ithink) an IF statement, but I am having problems with the errors, when I try the IF.
The macro below blows up at the ActiveCell.Offset(0, -1) = "109073X line with an error "Run-time error "1004" - Application-defines or object-defined error.
I've tried a number of things I've seen from this board but have not found a way past the error.
If the value is found, the macro moves to the else statment and stops at the ActiveCell statement with the above error. If the value is not found, then the macro goto NotFound.
Sub Macro2()
'
'Range("A1").Select 'Start from the home cell
Dim ValueFound As Object
Set ValueFound = Cells.Find(What:="Cirrus Reversals/CREDITS")
If ValueFound Is Nothing ThenGoTo NotFoundElseActiveCell.Offset(0, -1) = "109073X"'ActiveCell.FormulaR1C1 = "109073X"End If
NotFound:
End Sub
View 3 Replies
View Related
Jul 27, 2007
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 10 Replies
View Related
Feb 20, 2008
I would like to find a text string in a document, move the information in the cell to the right of this to another cell (say A1).
View 8 Replies
View Related
Apr 27, 2009
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 9 Replies
View Related
Nov 2, 2012
What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.
Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?
View 2 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
