Find Value In 1 Of X Worksheets & Return Corresponding Cell
Feb 24, 2009
I have a problem with VLOOKUP. The Value im trying to look up a value which is one of 5 sheets Called Con1 to Con 5. These Sheet names are in an Array called "ArrCons" the look up value range is also a variable and looping through column "C". The table Array is on Sheet "Con" Range "A:E", return column is 2 and the logical value is "false".
ActiveCell.Offset(i-1,2)=Application.VLOOKUP _(ArrCons(y)!C & i -1, Sheets("Con").Range("A:D"),2,False)
I am trying to return the SUM of a variable cell across multiple worksheets... This 3D formula works
=SUM('March 2012:December 2013'!E27)
I wanted the formula on the next row to ref F27 then G27 etc... (which I can't just drag down) so thought I'd try to "assemble" the formula I wanted using the INDIRECT function...
I tried this formula which doesn't seem to work...
=SUM(INDIRECT("'"&E6&":"&E27&"'!E27"))
NOTE: the script in the INDIRECT brackets returns the text string... 'March 2012:December 2013'!E27
I have a spreadsheet with many worksheets, everyone except the first representing a year. Every worksheet has twelve tables (the months), the first row of each table has the days of the month and the second row has the data that I put on a daily basis. The first worksheet calculates statistics using the data from the other worksheets.
I am looking for a formula that returns the date by finding the last populated cell and looking right above it, so I can use this info in the first worksheet to show when the spreadsheet was last updated.
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.
This time I'm trying to use the max value and row value at the same time, but it won't let me. Here's what I'm trying to do...
A B C D 1 a b c d 2 7 9 4 2 3 e f g h 4 8 2 7 8 5 i j k l 6 4 3 3 9
I want Cell A7 to return the letter before the highest value in column A... A7=e B7=b C7=g D7=l
I tried to do something using two cells, but even then I couldn't figure it out. I thought a ROW(MAX(A1:A6)) would give me the row and I could go from there, but that didn't work.
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.
I'm trying to write a macro where I match specific letters from one cell, with a column in another sheet so I can copy/paste adjacent cells. As an example I have a list of part numbers in column A of my Document1:
ex. C12932-1 C13226-2
I want to be able to find the corresponding part number in column A in Document2: ex. C-12932 C-13226
However the proplem is clearly that the part numbers are written differently. So I need to just search for "12932" or "13226"
construct a formula that finds a value in a range , then returns the cell address of that value.
Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.
I have tried the address & match function but gives me an N/A error.
I need to write a conditional formula that will look up in a 2nd worksheet the last coloumn with data (note that there will be some coloumns in the row that will have data and some that will be blank, I need the last column for that specific row to be the return result)
when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?
so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng
set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious) ok so say it picks up..... cell A69... how do i get it to save range A69
I would like to be able to find all cells in row 2 that are "greater than 0". Then I would like to return the coresponding job number in row 4. I need to create a list of each job with the number of hous spent on that job next to it. I may have 30 different jobs but only 5 jobs that have hours for the specific week.
How I attemted to solve this is to create a table that listed all the hours from the largest number in row 2 to the smallest. Then I found the job number corresponding to that number of hours. The problem with this meathod is that if two jobs have the same amount of hours then the first job number is returned for both values that are the same. See attached example.
I have this table, which can be seen as a basic custom gantt chart: KLRWo.png
And I would like to fill the A column with start dates, based on the first filled cell of the range on the same row, and the header value of its respective column (row 1). It's easier to show my expected result than write it actually:
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE: Sweater SWTR T-Shirt TSHT Jeans JEANS
COLOR TABLE: Blue BL Green GN Purple PL Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
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.
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
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.
spreadsheet 1: Lookup from Order numbers listed from A5:A177. requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2: Lookup value:Order number listed from F19:F191. Data search:AY19:CI191 return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
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 ...................
On " sheet 1" I have a cell A1 with a number 12550 in it. On the following 6adjacent cells from B1 to G1 there are specific values assigned to them. (Eg. $20, $30, $40, pork, 145, and spicy). There are currently 4 worksheets in the workbook and some of the worksheets contains the value "12550" in CellA1 of Sheet 1 while others don't.
I wish to search in a similiar column, from A1 to A50, throughout sheet 2, 3 and 4, for the value of "12550". Once the value is found in a sheet, the corresponding values to the 6 adjacent cells (from B1-G1 in Sheet1), are copied and pasted next to the "12550"cell. If there are no "12550" cells found in a sheet, the macro should continue as normal.
I have two worksheets containing four columns of data, example below. I need to match data found in any one of the four columns in worksheet B, against the data in the corresponding column of worksheet A, and if matched, return the result from the “Asset No” column of worksheet A (and return N/A or similar if none of the four columns could be matched)
Worksheet A Asset Serial Server IP No. 1234511111ABCABCBlank. 45678Blank.DEFDEF12345 7891033333Blank...45678
Worksheet B Asset Serial Server IP No. Blank.22222XYZXYZBlank. 45678Blank.Blank...12345 Blank.Blank.Blank...45678
In this example, I would expect to return the result 45678 against row 2, & 78910 against row 3, and receive a “not found” in row 1
I tried using IF & OR in an array formula, but could only get to a TRUE or FALSE result (was not able to reference the Asset Number).
I'm trying to compare 3 different worksheet in the same workbook and copying the unmatched values into a new worksheet. find the attached file. In the sample file, am comparing sheet1, sheet2 and sheet 3 with sheet1 as the base. the columns mite differ in their location but the column headers are same.. The difference, bolded cell, should be copied along with the header and the code, into a new file and has to be saved...
I am trying to track cost. A master purchase order sheet has a column with a number relative to an account. I have about 50 accounts and a seperate sheet for each account. I want to enter the account number on the master purchase order sheet and based on that number, have the following data on that row copied to the next available row in the sheet for that account.
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()?
I have a different worksheet in the same workbook for every "gig" that I book. Each gig contains the name of the musicians I staffed on the gig along with their salary+expenses For taxes, I need to summarize into a new worksheet how much each musician made. Ideally i'd see not only the total per name but itemized. So if 1 guy did 3 diff gigs, I'd see each row pertaining to his name.
Lastly, I'd like to include names that do not have multiple records because it's possible there is inconsistencies with the spelling of certain names.
