# 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.

## Excel 2010 :: Creating Bell Curve?

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.

## Excel 2010 :: Creating Sound Alert

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?

## Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

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.

## Excel 2010 :: Creating A Chart With More Than 2000 Dates (each Day)

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.

## Excel 2010 :: Creating Random Trending Data

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)

## Excel 2010 :: Creating Reports With Multiple Conditions

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"

## Excel 2010 :: Creating Charts With Average Line?

Apr 5, 2012

How I could create a chart (standard column) with an average line in Excel 2010?

## Excel 2010 :: Creating Extra Sheets With Macro?

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

## Excel 2010 :: Creating Scatter From Multiple Columns Using Ctrl

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.

## Excel 2010 :: Creating A Simple Line Marker Graph?

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%

## Excel 2010 :: Creating Bracket For Video Game Tournament

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.

## Excel 2010 :: Creating Mandatory Field Before Saving A File?

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.

## Excel 2010 :: VBA / Creating Multiple Workbooks From A Template And List Of Names?

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.

## Excel 2010 :: Creating Multiple / Repeatable Lists Of Random Numbers

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.

## Excel 2010 :: For Loop And VLookup Don't Work

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

## Excel 2010 :: VLookup To Return 2nd Matching Value

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

## Excel 2010 :: VLookup For First Non-blank Cell

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

## Excel 2010 :: VLookup Will Not Match Unless Use Sendkeys F2

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:
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] ........

## Excel 2010 :: VLookup And Formula That Copy Name Of Tab?

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?

## Excel 2010 :: Countif And VLookup Of Multiple Values

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‎

## Excel 2010 :: VLookup With Named Range Not Updating?

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

## Excel 2010 :: VLOOKUP Is Acting Case Sensitive

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
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))

## Excel 2010 :: VLookup Based On Values From Two Columns?

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
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
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

## Excel 2010 :: Values (from VLookup) Not Showing Up In Chart

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].....

## Excel 2010 :: Function To Create PDF

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"?

## Excel 2010 :: Function Error In VB

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

## Excel 2010 :: Random Numbers In One Function

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.

## Excel 2010 :: Copy And Paste Function?

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.

## Excel 2010 :: Random Numbers In One Function?

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.