Excel 2010 :: Creating VLookUp Function In VBA
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
ADVERTISEMENT
Jul 29, 2014
I want to create a bell curve for my team's annual appraisal. There are 12 people in my team. The scale is 1-5, where normally rating range from 2.9 to 4.0.
View 1 Replies
View Related
Feb 23, 2012
I would like to create a sound alert if the answer to an "if" statement is false. I am using Excel 10. Can it be done, and if so, how?
View 5 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Apr 26, 2014
I am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).
At the moment I cannot format the axis and minor/major units remain numbers.
I am using Excel 2010 and European date format.
View 2 Replies
View Related
Aug 11, 2014
Im looking to create random data for an athlete over about 4 months with daily tests, however when im using the =randbetween(*,*) function the data is too 'Random'. so im looking to create a trend in the data, like real athletes will have. is there a function in Excel 2010 that can do this?
The boundaries of the data are -336,416 (i know its quite a big range)
View 8 Replies
View Related
Jun 24, 2014
I am creating a report where there are multiple conditions and I am using MS Excel 2010.
Summary sheet will display
Date of Completion - If the exam has been taken already
In Progress - if the exam has been started but not yet completed
Not Started - if the exam has not yet been taken
Available data in Source sheet
Completion_Date - Date when the exam was taken or "Blank" if the exam is still In Progress
Completion_Status - Completed & In Progress only (all subjects that are not in the Source sheet shall be tagged as "Not Started"
View 4 Replies
View Related
Apr 5, 2012
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies
View Related
Nov 7, 2012
I have headings across the sheet "Inventory" in cells E5:AA5 with further headings down the sheet D6:D40.Data Validation drop down list is in cells E6:AA40.I want to be able to place a number 1 or number 2 in any cell in the range C6:C40 in the "inventory" sheet and with a macro create 2 new sheets.Sheet 1 will contain all the headings which had a 1 in any of the cells in the range C6:C40 and sheet 2 will have all the headings which had the number 2 from the range C6:40.Both sheets 1 & 2 will contain all the headings from "Inventory" sheet in cells E5:AA5.The data validation drop down list is not required in sheets 1 and 2 but if "inventory" sheet does get updated that it also updates sheet 1 and sheet 2.... Version i am using is Excel 2010
View 1 Replies
View Related
Jun 12, 2014
I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.
When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.
I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.
I'm using excel 2010.
View 3 Replies
View Related
Mar 2, 2014
I'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:
Seperation First target Second target
0 59.00% 0.00%
2 64.28% 40.62%
4 62.68% 48.99%
6 60.81% 65.83%
8 61.82% 70.21%
View 6 Replies
View Related
Nov 11, 2013
I'm trying to create a bracket for video game tournaments and I'm having trouble with some of the functionality I want it to have.
Right now, I have cells B7 and B9 that will pull player names from another check in sheet. I have another cell, D8, that I would like to have a drop down list in containing the contents of B7 and B9. Everything I see online says to use Data Validation, but apparently that tool can't make a list using cells that aren't next to each other, so that won't work. How else can I tell excel how to populate this list? It is vital that the cells be apart from each other because the visual formatting is necessary for displaying the bracket on a projector so that the players can see their match assignments.
In addition, I would like to have another drop down menu in another cell with a list of valid bracket sizes (4, 8, 16, 32, etc.), and have the cells of the sheet either be visible or invisible depending on how many max players can enter. In other words, if columns B, D, F, and H correspond to the number of rounds it will take to finish the tournament (3, 2, 1, and 0, respectively), I would like the sheet to display only the cells necessary for the tournament, pull player names into the left most cells needed from another worksheet used for sign ups, and auto populate all of the columns to the right of the column for first round with drop down lists for the players who could have won that match in the previous round.
View 1 Replies
View Related
Aug 22, 2013
coding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
View 1 Replies
View Related
Aug 20, 2013
The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10
I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks
I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.
The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.
I'm using Excel 2010.
View 9 Replies
View Related
May 19, 2014
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
View 1 Replies
View Related
Aug 21, 2014
The for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?
Dim i As Integer
For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i
View 3 Replies
View Related
Mar 10, 2012
Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second and third values in the Formula result?
In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.
I'm using Excel 2010
ABCDEFG1=SUM(IF($A$2:$A$8=A2,1,0))=VLOOKUP(F2,$A$2:$B$8,2,FALSE)
2JOHN13BILL12BILL11BOB14MARY11FRED15JOHN23JOHN16FRED11JOHN17BOB11JOHN18JOHN33MARY1
View 9 Replies
View Related
Feb 5, 2013
I have a table in columns A-D. I am trying to perform a vlookup to return the first non-blank in columns B-D based on the lookup value in column A. Columns F-I are what I am looking for the formula to do. I think I am close with the following array formula but not quite there.
Excel 2010LMN12#N/A3Sheet1Array FormulasCellFormulaM2{=INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),
MATCH(TRUE,INDEX(INDEX($B$2:$D$9,MATCH($F$2,$A$2:$A$9,FALSE),0)<>"",),FALSE))}Entered with Ctrl+Shift+Enter.
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Excel 2010ABCDEFGHIJKL1PFQPFQ212341234CRWEWS312343456
WETXDR41234CR5123461234WE712348123491234WS1034562)
What I want it to return, vlookup of first non-blank
113456WE123456DR133456143456TX153456163456173456183456191)
What table looks like20Sheet1
View 4 Replies
View Related
Sep 24, 2013
We have a business system which has some very disjointed reporting. To fix this I have multiple extracts that are then combined into a single report. I then add Year & Period from a lookup of a date in a particular field. Unfortunately most values are returned as #N/A but if I manually go into the sheet and select an offending cell and click then magically the formula then corrects itself.
So I thought I would be clever and use Sendkeys {F2} as part of my code. Unfortunately this is a bit hit & miss as it works on some but not on others. Also the Sendkeys piece repeats itself leaving my frontsheet a 1000 rows from the start. This is easily fixed by selecting cell A1as the last step.
My question is there methods in VBA by not using sendkeys that will achieve the same result and is consistent. I am using Excel 2010.
Code:
Sub Add_Calendar()
Dim cell As Object, c As Range
'setup for calendar data population
' Range("H1") = "Created Date"
Range("N1") = "Year"
Range("O1") = "Period"
'copy the format
[Code] ........
View 1 Replies
View Related
Nov 25, 2013
I have Excel 2010 & Windows 7
I am using the following formula to copy the name of the tab - =MID(CELL("filename",C26),FIND("]",CELL("filename",A1))+1,6)
I have a marco that creates a tab from a Master file, and I need to copy the name of the tab into cell A2, using the formula above. Then I use a vlookup on this cell, to get the data needed for about 10 other fields. I have these vlookups in the Master file. The vlookup doesn't work all the time. I get a #NA answer. Sometimes it does work. When I manually type in the project number on the tab, the vlookups work.
Sample of the vlookup: =VLOOKUP(A2,'AC-Program'!$A$1:$H$58600,2,0)
I was trying to find a way to use a value in cell A2 instead of the formula above, but I don't want to have to manually update it, I need it to be generated when the Marco generates the tabs.
Here is my macro:
Sub CreateSheetsFromAList()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")[code]......
Is there a way to add this function into this macro, and it will leave this field as a value instead of a formula?
View 2 Replies
View Related
Jan 15, 2014
I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.
I have attached a sample workbook as a reference : Sheet1.xlsx
View 8 Replies
View Related
Oct 5, 2011
There is a formula
=vlookup(a1,Named_Range,2,false)
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
View 1 Replies
View Related
Mar 2, 2012
I am using the this formula to look up data using 0 to show not found. VLOOKUP is acting case sensitive. The Master Array data is in caps and the input is in lower case. There are no spurious spaces. Here are the results:
Code Name
0 River Branch Foundation = not found
X RIVER BRANCH FOUNDATION = found - in array as caps
The formula:
=IF(ISNA(VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3053,2,FALSE)),0,VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3054,2,FALSE))
View 4 Replies
View Related
Jun 4, 2012
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
View 5 Replies
View Related
Oct 22, 2012
I have excel 2010....
I am having problems creating a chart off of the following data. Each of the cells which have a numerical value are v-lookuping from another sheet. It needs to remain this way as when I switch brands (through data validation), the numbers will update and the chart should as well. Right now, the chart shows these values as zero. I have done this a million times in excel 2007, but now with 2010, I keep having this problem
2010/Jul
2010/Aug
2010/Sep
[Code].....
View 2 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 21, 2011
Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.
Code:
Function GetSheetName(ByVal m_sFormula As String) As String
Dim m_sSheet As String
m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2)
If InStr(m_sSheet, "'") Then
m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2)
End If
GetSheetName = m_sSheet
End Function
View 1 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 2 Replies
View Related
Apr 18, 2012
The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.
When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.
As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.
I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.
View 2 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 1 Replies
View Related
