MACRO Lookup Columns Of Current Sheet And Matches It With Table In Tab Then Returns Hourly Pay In Column
Mar 5, 2014
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm‎
View 4 Replies
ADVERTISEMENT
Jan 26, 2010
a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1. Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file. I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3.
Exp
Sheet 1, report to be printed
A1 = last name
A2 = first name
A3 = month
Sheet 2, data is held here
Need to pull data in column “M”
View 2 Replies
View Related
Oct 27, 2008
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies
View Related
Aug 26, 2009
Worksheet #1:
Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...
Worksheet #2:
In cell A1 is the number "1"
In cell A2 is the number "7"
I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N
So my result on Worksheet #2 should be...
Cell B1 shows the letter Y
Cell B2 shows the letter N
View 2 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
May 7, 2014
I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,
The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.
{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}
View 5 Replies
View Related
Jan 16, 2014
I have a table (A1:C5)
A1:C1 contain the table headers
A2:C5 contain the data
A6 contains the value I want to search for I use this formula to return the column header of A6 A6=INDEX(A1:C1,SUMPRODUCT((A2:C5=A6)*COLUMN(A2:C5))) Now, if there are duplicate values in A2:C5, this will return more than one column header What I need is a vba code to view a msgbox with all column headers returned in this case
View 1 Replies
View Related
Feb 1, 2010
I have a table with 3 columns. See below for data (ignore dashes):
COLUMN X-----COLUMN Y-----COLUMN Z
Project 1---------21 Dec 09---------$70
Project 2---------21 Dec 09---------$85
Project 3---------21 Dec 09---------$90
Project 1---------1 Jan 10-----------$85
Project 2---------1 Jan 10-----------$95
Project 3---------1 Jan 10-----------$110
As you can see, COLUMN X contains Project Names, COLUMN Y contains Dates and COLUMN Z contains Monetary Value.
Here is what I need to do: I need a formula/function that will lookup "Project 1" in COLUMN X and find the latest corresponding Date in COLUMN Y (for this example, 1 Jan 10) for Project 1 and then display the corresponding Monetary Value from COLUMN Z (for this example, $85).
I have tried using VLOOKUP, but since the data must be sorted it won't work. I tried using INDEX(MATCH), but can't get it to work. My goal is that the cell will be dynamically updated every time a user inputs updated data for a certain project based on the latest date.
View 3 Replies
View Related
May 26, 2006
In my current worksheet, IF(0 = "CS") returns TRUE, but on any other file this same argument returns FALSE. The cells' formats are both general. I want it to be FALSE.
View 2 Replies
View Related
Nov 4, 2013
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
View 1 Replies
View Related
Dec 23, 2007
I have a workbook with two sheets imported from different sources. Sheet 1 is an AdHoc query from an Oracle 10g based program. Sheet 2 (PBIC 8 in my file) is a report generated from a property accountability program. The information from Sheet 2 is manually inputted into the Oracle program that generated Sheet 1.
I would like to have a macro that would compare the cell contents in columns "RegistrationNbr" (column S) and "SerialNbr" (column T) with the cell contents in Sheet 2 (PBIC 8). The match in Sheet 2 could be an identical match or part of a longer string. The matching cells in both sheets should be highlighted.
One thing to be considered is the column names in Sheet 1 will always be the same but the column letter designation may change base on the fields selected in the AdHoc query.
I didn't realize my file was too large and didn't upload.
View 4 Replies
View Related
Mar 21, 2007
I've got two worksheets ("June" & "July"). On both worksheets, column A is comprised of ID numbers and column B contains dollar amounts. I need to compare the ID numbers in Column A on each worksheet, and if they match I want to copy the ID number and the amount to a third worksheet ("Results").
View 3 Replies
View Related
Jun 20, 2014
I am trying to figure how to show in one pivot table a current column and a proposed column. I have 15k rows of data. My data columns are employee, month, task, hours, proposed month. I can get a table that has months as columns and tasks as rows with sum of hours. What I would like to do is incorporate the proposed month, so that it shows hours in the months by current and proposed. That way my result would be January current, January proposed columns etc. I can change the propsed months by formulae so I want to play with the proposed task month the refresh the pivot table to see the results.
View 2 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Jun 23, 2014
I have a macro that I am working on which I have gotten stuck on. It compares Column A and Column C and any numbers which match are pasted into Column D.
What I am getting stuck with is I need to copy and paste the data for column B to Column E as well. So in other words.
If column A and B match then the number from Column A that matches B will be pasted in column D along with the value that is right next to it in Column B.
Attached File : MatchingMacro.xlsm‎
View 11 Replies
View Related
Sep 11, 2012
I'm trying unsuccessfully to write a function that looks for a column in a pivot table and returns the entire column as a text string. The function should take a pivot table and a text string as an argument. The text string is the name of one of the columns. The function needs to return the entire column (excluding the header and footer rows) as an array.
In the example I pasted below, the function should take the pivot table (PivotTable1) and a color, say "Red", as arguments, and then return the values in the Red column as a 48x1 single vector array, in the order shown, starting with 43 and ending with 1. I can only post a text version of the spreadsheet; I was not able to post the actual spreadsheet.
Average of Cost
Color
Date
Hour
[Code]....
View 3 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Nov 29, 2006
I have a large worksheet in drive C: called 'hits.csv' that has manufacturer part numbers in column G and column L.
I need macro code to find all rows in the worksheet where the part number in each cell in column G is exactly the same as the part number on the same row in the corresponding cell in column L. The macro should then delete/remove all rows where there is no exact match.
Note: The part number in every cell in column G is the only thing that is in each cell in that column. However just to warn you that the part number in every cell in column L is at the beginning of each cell, followed by a space and then a description of the part.
Once it has done the above, I would also like this macro code to open a worksheet called 'partnumbers.csv' in drive C:. It should then compare the part numbers in column A of this file with the part numbers in the above worksheet ('hits.csv'). The cells in Column D of the file 'partnumbers.csv' all contains a price. Where the macro finds an exact match on the part numbers in these 2 files, it should copy the price from the cell in column D in the file 'partnumbers.csv', into Column K of the other file, 'hits.csv' where the match exists.
View 9 Replies
View Related
Jul 4, 2014
I'm trying to create a macro that will look at the values in one column (column G) and whenever there is a new value, it will copy the data from 2 other columns (I and J) into a new sheet with the original column as the title. I can't think of a better way of explaining that so I've attached a sheet with 'Raw data' being the source sheet and 'Outcome' being the hoped for final product.
I have color coded the sheet to show where the data is coming from, but do not want the colors in the actual sheet.
View 8 Replies
View Related
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
Jun 5, 2014
I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?
I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.
i.e.
A B C D
territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555
and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.
View 3 Replies
View Related
Jan 30, 2012
I'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
View 6 Replies
View Related
Apr 2, 2009
How do I change this macro to refer to the current sheet name instead of "FullScreen (2)"?
View 4 Replies
View Related
May 16, 2011
I have to excel files, both have the same data. I created a pivot table macro on the first file. I want to be able to open the second file and use the shortcut to create the pivot table. the problem is that it refers back to the original workbook I created the macro on. How do I change the code to make it refer to the current workbook?
Code:
Sub Pivot()
'
' Pivot Macro
[Code]....
View 1 Replies
View Related
Nov 18, 2013
I have a workbook with four tabs or four sheets.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
View 6 Replies
View Related
Jul 13, 2009
I wrote this macro that will work on two named sheets but i want to copy the column from the active sheet:
View 4 Replies
View Related
Mar 1, 2008
From sheet1 on a checkbox click I am attempting to post the system date into sheet3 cell c3. If c3 already contains data then I want the cell selection to go down to the next row and post the date there. I if cell c3 has no data then it posts fine but if I need to goto the next row then I get a runtime error "object doesn't support this property or method".
If Me.GCN = True Then
If Not IsEmpty(Worksheets("GCN_Paid").Range("c3")) Then
Sheets(3).ActiveCell.Offset(1, 0).Select
Sheets(3).Range("c3") = Date
Else
Sheets(3).Range("c3") = Date
End If
End If
View 3 Replies
View Related
Feb 22, 2013
Code to enter the current date in column if the values are entered in the adjacent column's.
View 2 Replies
View Related
May 16, 2014
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies
View Related
Apr 28, 2007
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999
FY Apples Pears
2004/5 5 4
2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
View 7 Replies
View Related
