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)
View 9 Replies
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.
I have attached a file to make this easier. Basically I would like to search "Points Scored" (B2:W9) for the highest score (which I already have done) and when it is found return the team's name that is associated with it. Then do this for the second, third, etc. as it can be seen in the "Main" sheet. I'm thinking an index/find approach, but I just keep getting "N/A".
View 7 Replies View RelatedI 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.
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
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
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:
WiMZH.png
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 RelatedI 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.
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.
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 ...................
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 RelatedI 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).
Sheet2 has columns with dates as headings. I add data under each date as appropriate. I would like a summary page for Sheet1 that contains only the most recent column data from Sheet2 (and other sheets, of course). For instance, if I have dates for columns A-E on Sheet2, but only A and B have data under them, I would like Sheet1 to contain the data in Sheet2!B, but not Sheet2!A because that is old data. Sheet1 will only contain the most recent data while sheets 2 through "n" will have historical data. Attached is an example of what I mean...
View 5 Replies View RelatedI need to write entries into an open spreadsheet with data input on a userform.
i need to use the xlup facility to find the last used row in the spreadsheet, select the next line and then enter the data, but how to return to VBA the actual cell reference that has been selected after doing the xlup and down one row. I need to be able a total potential 31 rows of data from the userform.
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?
I have two work sheets I'm using. Sheet1!A column contains a list of customer names like "Smith, John".
Sheet2!A contains the same list of name with additional information found in the row. For example, you will find "Smith, John" in Sheet2!A3 and John's phone number in Sheet2!B3
In Sheet1!B it to find the associated telephone number based on matching criteria from Sheet1!A and Sheet2!A
For example, Sheet2 has the following data.
[Code] ....
On Sheet1 I have the following names. You'll notice some of the names repeat.
[Code] ......
In column Sheet1!B I would like it to scan column A from sheet2 and display the data show in column B. So it would look like this-
[Code] ....
You'll notice some names repeat but it always displays the correct number from Sheet2.
I have a userform that I am using to populate a column with data. I have the following code to find the next blank cell on the first row to enter the data from the first textbox in the userform
ActiveSheet.Range("av1").End(xlToLeft).Offset(0, 1).Value = TextBox1
I was then going to populate the rest of the cells in the column by changing the range "A1" to "A2" and so on. The problem I have is that not all of the cells have a compulsory entry so when the end(xlToLeft) function may not always end in the same column and the data will be staggered.
First Entry
A B C D E
1X
2X
3X
4
5X
Second Entry
A B C D E
1XY
2XY
3XY
4Y
5XY
What I want to do is find the first blank cell in the first row, as that will have a compulsory entry, and then fill the rest of the cells in the same column. So if the first blank cell is D1 i want to go down then D2,D3,D4 etc.
I can do it going across the rows but cannot figure it out using columns.
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, "A")).Select
I've got a spreadsheet with various amounts in cells A3:L5. I want to find the last non-blank entry in each row but only where the entry is in a column headed "Guaranteed PRB" (found in row 1). Then I want it to return the column header title found in row 2, which is a date.
I've attached a sample of the spreadsheet with the expected answer in column M.
I've got as far as formula:
=LOOKUP(2,1/(a3:l3<>""),$a$2:$l$2)
how to also make it look at row 1's headers too.
I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:
=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)
but all I get is hashN/A?
I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria. For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".
Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom
Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom
I'm sure this is elementary, but I can't seem to figure it out.
I have a chart:
ABCDE126143199167233153143310351018220110246817085151581166230162692149206173711417214213281273781662029132178200255
I need a formula to find the maximum value in C1:E9 and return the corresponding number in column A. In this instance, the answer would be 12. I've been trying to make it work with a combination of Offset and Max formulas, but since the maximum could be in any one of three columns, I don't know how to make it work.
I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.
View 2 Replies View RelatedI am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.
So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
I am trying to create a run balance sheet (see attached sheet). Column C has a list of job numbers, with column D showing the reqd quantities. Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H. My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.
Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6.
When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value.
I have tried VLOOKUP and INDEX, but cannot get the thing to return.
i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.
here's the attach file i just trim it down.
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies View RelatedHow to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
