activecell & Vlookup

Is there a way to rewrite a vlookup based on activecell?

=VLOOKUP("activecell",Sheet1!A:B,2,FALSE)
where in it picks up value from the active cell?


0
flow23 (46)
12/22/2005 1:57:02 PM
excel.misc 78881 articles. 3 followers. Follow

1 Replies
278 Views

Similar Articles

[PageSpeed] 24

Right click on your worksheet and put something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1") = ActiveCell.Value
End Sub

change A1 to any blank cell and refer to that cell in your formula.

HTH
JG


-- 
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=495494

0
12/22/2005 3:23:30 PM
Reply:

Similar Artilces:

vlookup function
I am doing grading using excel. For grade percentages, I would like to have the letter grade automatically calculated rather than figuring out each student individually. The grade percentages are broken into 10s, each one gets the next letter and after 60 is an F. How do I enter the formula to automatically process the letter grade. Any help you can give me is much appreciated. Thanks.:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi Blinkerbox, I've ...

Vlookups
I have created a general order form in excel using vlookups, i would like to know if in repeating the function throughout a colomn is it possible to not have the #N/A show if there is nothing entered into it You could use Format|conditional formatting to hide errors (white text on white fill). Or you could change your =vlookup() formula: =if(iserror(yourformula)),"",yourformula) Shaya M wrote: > > I have created a general order form in excel using vlookups, i would like to > know if in repeating the function throughout a colomn is it possible to not > have the #N...

Defining an Activecell
Each month I add new data to an existing sheet. I then want to run a macro to insert a new column and a formula. Trouble is this data increases rows each month and I need to be able to identify the final row each time so I can copy the formula down. ie in Jan the data finished at row 100. In Feb it finishes at row 200. I need the macro to be able to know to copy the formula down to row 200 in Feb for example. Hi Ant You can use this function fir finding the last row with data on thye worksheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:=...

Vlookup Help #6
Hi, please help me out: i have two excel files: First File contains few numbers in A column: eg: 5106134 5123866 773832 Now, i have another file which conatins some codes for these numbers but please note that the this file may conatain thie same number mor than 1 time as well, and all the times the code may be same or may not eg: Second File: 5106134 SRST 5123866 ACSS 5106134 RPRG 773832 CHFO 5123866 SRST 5106134 CUED 5123866 ACSS 5123866 CUED NOW, I AM APPLYING THE FORMULA IN FIRST FILe (VLOOKUP), AND I M GETTIN THE RESULT AS: 5106134 SRST 5123866 ...

#N/A error with VLOOKUP
My VLOOKUP was working fine, but now I have added some text boxes and pictures around the sheet itself. I have allowed for the extra column, but the VLOOKUP isn't working anymore. Does it not like pictures? I haven't included these in the area it is looking at, so it shouldn't get confused. Unlike me... -- Michelle Tucker Hi Michelle the #NA error can occur when the VLOOKUP has nothing to lookup ... so my first suggestion would be to check that the VLOOKUP is still referencing the correct cells in your workbook. if that doesn't help, please post your exact VLOOKUP sta...

Vlookup no working
I have only 2 worksheets in column A is the item # which sometimes has a letter associated with it as in 815NB in column B is the desctiption. This formula works fine when the cell only contains a number but will not including the letter. I can manually retype the cell info and the formula will find it even with the letter association. Worksheet 1 has 14000 rows and Worksheet 2 has 3000 rows, so I only need some of the information from the 1st worksheet. The formula I'm using is =IF(ISNA(VLOOKUP(A1,LKF!A:B,2, FALSE)), "", VLOOKUP(A1,LKF!A:B,2, FALSE)) Sorry for the length. I ...

VLOOKUP and multiple rows ?
I have two worksheets: #1 contains client numbers and data. client no. data 1 xyz 2 abc 3 def 4 wed #2 contains client numbers and the employees attached to the client. client no employee 1 joe 1 tom 2 jim 3 ann **Here's my problem! Is there a way to do a lookup on spreadsheet # 1 to bring in the employee data from spreadsheet #2 (a normal VLOOKUP) and if there are mutiple employees to either concatenate the data-> client no employee 1 joe,tom or put it...

VLookup?????
=IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in tableB) This is the code i have. I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B. Also if there is 2 occurences of column B in table B, i want the field in table A to show both values. Any ideas? (if u can understand what im asking) -- Hazy ------------------------------------------------------------------------ Hazy's Profile: http://www.excelforum.com/member.php?action=g...

Activecell problem #2
I have a really basic question I am trying to print a certain area which starts at a certain cell which may differ every week. I have the code to get to the bottom right of the area I want to print but then I want to select the area, from the active cell to A3: 'go to the bottom cell in colum L Range("L100").End(xlUp).Select ???????? what goes in here?????? 'Print the selected area ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Any help appreciated. remove your select line and add range(Range("A3"),Range("L100").End(xlUp)).Select -...

Vlookup and Match
I can not seem to get the Vlookup with Match to work. I would appreciate any help. I keep getting the wrong answer in the lookup. thank Alfarata +-------------------------------------------------------------------+ |Filename: VlookupTable.zip | |Download: http://www.excelforum.com/attachment.php?postid=4749 | +-------------------------------------------------------------------+ -- Alfarata ------------------------------------------------------------------------ Alfarata's Profile: http://www.excelforum.com/member.php?action=getinfo&use...

VLOOKUP with 2 conditions
Hi there, Any ideas on how i can search based on two fields ? Scenario is as follows: Brian Red Fire Brian Blue Water john Red Volcano John Blue Hurricane I want to search For "Brian" which is equal to "Blue" and the answer I want to obtain is "water" Any ideas will be appreciated. I know you can concatenate Brian & Blue & apply a vlookup but i know thios can be done by the use of an array. Thanks in advance. Brian Hi try the following array formula =INDEX(C1:C100,MATCH(1,(A1:A100="Brian")*(B1:B100="Blue"),0))...

VBA? Activecell formatting
Looking for a simple VBA macro for formatting three cells starting wit the active cell. Want to use ctrl-d to select activecell, then next two cells to th right and then fill all three cells with color red -- Message posted from http://www.ExcelForum.com Just a single line of code will do it: Range(ActiveCell, ActiveCell.Offset(0, 2)).Interior.Color = vbRed HTH, Nikos "click4mrh >" <<click4mrh.1bmwdk@excelforum-nospam.com> wrote in message news:click4mrh.1bmwdk@excelforum-nospam.com... > Looking for a simple VBA macro for formatting three cells starting with >...

Vlookup #14
I have these data: EmpID table (contains only empid): EmpID 1415 (cell a1) 1500 (cell a2 etc..) 1587 1608 1800 1815 1925 Dept Table (contains deptid and the range of empid - and has the range name "DEPT"): From Empid To Empid Deptid 1400 1499 100 1500 1599 200 1600 1799 300 1800 1899 400 1900 9999 999 The result I'd like to see is: EmpID Deptid 1415 100 1500 200 1587 200 1608 ...

VLookup results in #n/a
The results of a Vlookup will be #N/A in cells that do not find a match. I saw someone write a forumula that would sum the column while overlooking the #N/A cells but I don't remember how it was written. Any help will be greatly appreciated. Thank you You could have a formula like: =if(isna(lookup(a,range,1)),0,lookup(a,range,1)) It basically states that if you would get a #N/A result, then show 0, which would not affect a SUM. Andr´┐Ż "Jean" <jepperson@zebco.com> wrote in message news:096801c34ad7$77c9e510$3501280a@phx.gbl... > The results of a Vlookup w...

ActiveCell
I am trying to achieve If ActiveCell In Range ("C22:K22,C24:K24") Then but I require assistance with the correct syntax. Thanks Sandy Try something like this: IF Not Intersect (ActiveCell, Range("C22:K22,C24:K24")) is Nothing THEN (Not really intuitive, is it?) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message news:5B6BB6C6-B6CB-46A8-B998-C8BA24EA9FFB@microsoft.com... >I am trying to achieve > > If ActiveCell In Range ("C22...

Changing the ActiveCell
I want to Select and copy several block of data based on where the initial ActiveCell is locate. The following copys the first block. Dim StartingCell As String Dim EndingCell As String Dim RowOffset As Integer Dim ColOffset As Integer RowOffset = 2 ColOffset = 3 StartingCell = ActiveCell.Address EndingCell = ActiveCell.Offset(RowOffset, ColOffset).Address Worksheets("Sheet1").Range(StartingCell & ":" & EndingCell).Copy _ Destination:=Worksheets("Sheet2").Range("Jeff") Now, "Logically" I ...

VLOOKUP not working correctly!
hi I have been using VLOOKUP on a few reports recently and it has been working great, however, cannot seem to get it working on a particular field. I am linking from a master file and linking to current workbook. When you select the job number in the workbook it populates all the address fields etc fine from the master file, and also copies the name ofr the Project Manager in (Cell Ref: D25), however from here I have a table that details the mobile numbers and emails address of each on seperate tab of current workbook called "Internal Contacts" thus: =VLOOKUP(D25,'Internal Con...

Vlookup #14
Ok I am still confused. This is what I have. Sheet 1 I have a list of Full names Column A Column B is empty Column C is empty with addresses in Column D Sheet 2 List of names Full names in Column A List of User names in Column B I want to match the full name on sheet 1 with the full name on sheet 2 and place the corresponding user name in the empty column B on sheet 1 Please help! Bud --- Message posted from http://www.ExcelForum.com/ Hi Bud put the following in B1 on your sheet1: =VLOOKUP(A1,'Sheet2'!$A$1:$B$999,2,0) copy down HTH Frank > Ok I am still confused. This ...

VLOOKUP and RAND question
Hi, I have a question regarding the VLOOKUP, RAND, and maybe IF function in Excel. I would greatly appreciate it if anyone can help with m problem. I have 2 columns of data and would like to use a formula fo Column 3. Column 1 = numbers 1, 2, 3 Column 2 = numbers 500, 1000, 1500 associated with Column 1 I would like Column 3 to use the RAND function to randomnly pick fro the numbers in Column 1, then return the value in Column 2. I'm no sure how to do this. Since there is equal probability of the numbers in Column 1 to appear can I just use the RAND function for Column 2 and skip ...

vlookups (complex)
In my excel file the worksheet Master Fe contains A1:EP4000. I need to lookup and return 4 sets of 3 values for each row in this worksheet located in columns CK,CN,CO CX,DA,DB DK,DN,DO DX,EA,EB (call them set 4,3,2, and 1 respectively). The reference lookup value used for the row is located in column A. In the same excel file the array worksheet Re-Arrange contains A1:H8000. This array is sorted in ascending order first column A then descending order column B. The lookup value is located in column A and the number 4,3,2,or1 is located in column B depending on how many times the ...

Excel Slow When Opening Linked Files with VLOOKUP Functions with >
We had had a few instances of folks lately having problems with slow response opening their excel files from shortcuts on their desktops. When they originally open the files (maybe linked with 4 other files), but have VLOOKUP functions, it is extremely slow. They cannot click the menu or do anything for several minutes. We are on Windows 2000 SP4. In my testing, it only happens with excel shortcuts. Opening the file from favorites or opening the file from within excel works fine. They mentioned this started happening some time this year and are finally fed up with this.. These are ...

vlookup or what?
I have a little database which arrives periodically, and I would like to chart the number of members by nation. I can't recall ever having this opportunity before. The nations are standard ISO codes (UA, UK, US, UY) and not numeric. Is there a way to organize these entries somewhat automatically for charting? Alternately, I wondered if I filtered the nation column, is there a way to export all the unique entries, preparatory to counting this by hand (so to speak) . . . Any advice would be gratefully appreciated. -- Regards, P D Sterling New York, Texas & Texas, New York PD,...

Help with vlookup #3
using 2003. I am looking up a number (stored as a string) on another worksheet and returning another series of numbers (also stored as a string) - ie lookup return 411140 263791-411140E Problem - Excel formats the returned data as: 2.63791E+11. It apparently sees it as a mathmatical subtraction of an exponential number even tho it is a text. It doesn't seem to matter how I format the cell. When I format it as text, the formula does not work. (requires General to execute formula). Executing a "text to columns" does the same thing. I need the returned data to show - 263...

if and vlookup functions
I need to do a function that will look for one of three criterion in a cell, if condition a,b, or c is met, then I need to lookup a person's name and then assign them to a team. So far I've tried the following formulae, but it turns up NA or TRUE. =if(f96="teamA"),vlookup(a96,TeamA!$a$4:$f$100,3,false), if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,false), if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,false) Please advise as I'm quite new to this. Regards. try this idea =IF(OR(D4="a",D4="b",D4="c"),VLOOKUP(D4,...

Selecting a range relative to ActiveCell, copy it to second Workshett within Workbook
This works through "ActiveCell.Offset(0, -8).Activate" but then doesn't. Any help appreciated. Sub SortforMember() Worksheets("Sheet1").Activate Worksheets("Sheet1").Range("I1").Select Worksheets("Sheet2").Activate Worksheets("Sheet2").Range("a2").Select Do Until Cell = "Null" Sheet1.Activate ActiveCell.Offset(1, 0).Activate If ActiveCell = "Yes" Then ActiveCell.Offset(0, -8).Activate Range(Cells(1, 1), End(x1ToRight)).Copy _ desti...