Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Using activecell in vlookup

Hi, I want to create a vlookup statement (just in Excel; not via a macro) but
I want the vlookup value to be whatever cell the user has selected (I think
this is defined as the activecell, right?).

So, for instance, if the user has selected cell A3 then my formula would be
vlookup(a3,b1:b10,1,false). But, if the user then selects cell A10 then the
formula would automatically change to vlookup(a10,b1:b10,1,false).

Can that be done?

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default Using activecell in vlookup

Yes, but you will need to use a bit of VBA. Also, before beginning, a word of
caution that changing a formula everytime you change selection in a worksheet
could lead to odd results. But here we go:

In your VLOOKUP, replace the cell reference with a name, such as MyRange
Example:
=VLOOKUP(MyRange,A:C,2,FALSE)

Now, right click on the sheet tab, go to view code. Paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=ActiveCell
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"csdjj" wrote:

Hi, I want to create a vlookup statement (just in Excel; not via a macro) but
I want the vlookup value to be whatever cell the user has selected (I think
this is defined as the activecell, right?).

So, for instance, if the user has selected cell A3 then my formula would be
vlookup(a3,b1:b10,1,false). But, if the user then selects cell A10 then the
formula would automatically change to vlookup(a10,b1:b10,1,false).

Can that be done?

Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default Using activecell in vlookup

That is FABULOUS! Thanks so much!

"Luke M" wrote:

Yes, but you will need to use a bit of VBA. Also, before beginning, a word of
caution that changing a formula everytime you change selection in a worksheet
could lead to odd results. But here we go:

In your VLOOKUP, replace the cell reference with a name, such as MyRange
Example:
=VLOOKUP(MyRange,A:C,2,FALSE)

Now, right click on the sheet tab, go to view code. Paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=ActiveCell
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"csdjj" wrote:

Hi, I want to create a vlookup statement (just in Excel; not via a macro) but
I want the vlookup value to be whatever cell the user has selected (I think
this is defined as the activecell, right?).

So, for instance, if the user has selected cell A3 then my formula would be
vlookup(a3,b1:b10,1,false). But, if the user then selects cell A10 then the
formula would automatically change to vlookup(a10,b1:b10,1,false).

Can that be done?

Thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If activecell.column = variable then activecell,offset (0,1) Battykoda via OfficeKB.com Excel Discussion (Misc queries) 1 October 2nd 07 08:05 PM
ActiveCell or ActiveSheet Launchnet Excel Worksheet Functions 1 July 20th 07 07:46 PM
ActiveCell in a Range? Filo Excel Discussion (Misc queries) 3 May 22nd 07 09:52 PM
activecell & Vlookup flow23 Excel Discussion (Misc queries) 1 December 22nd 05 04:23 PM
Defining an Activecell Ant Excel Discussion (Misc queries) 6 October 3rd 05 05:32 PM


All times are GMT +1. The time now is 12:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"