Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 4th 07, 03:22 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 1
Default How do I reference a cell as PART of a vlookup "Table_Array" locat

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?

  #2   Report Post  
Old January 4th 07, 03:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default How do I reference a cell as PART of a vlookup "Table_Array" locat

=vlookup(a4,indirect("'" & b1 & "'!A3:b78"),2,false)

I used the & operator instead of the =concatenate function, but that would work,
too.

-Rocket wrote:

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?


--

Dave Peterson
  #3   Report Post  
Old January 4th 07, 03:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 6
Default How do I reference a cell as PART of a vlookup "Table_Array" l

Works perfectly. Many thanks Dave!

"Dave Peterson" wrote:

=vlookup(a4,indirect("'" & b1 & "'!A3:b78"),2,false)

I used the & operator instead of the =concatenate function, but that would work,
too.

-Rocket wrote:

Basically, I have a matrix, and the top axis is the same as the name of other
pages in my workbook. The left axis are components that are on multiple pages
(the pages named in the top axis). I want to do a vlookup to populate the
matrix, and I need to reference the top axis as part of the vlookup formula:

=VLOOKUP(A4, (CONCATENATE("'", B1, "'!$A$3:$B$78")), 2,FALSE)

Thus, for worksheet named by B1, for array A3:B78, lookup the value in A4,
and return the value in the B column of the above array.

The trouble occurs when I try to reference the other worksheet via the value
in B1. Proper syntax from the "ADDRESS" function is '101'!$A$3:$B$78, which
my CONCATENATE function returns when run separately. Also, I have tried the
"INDIRECT" function surrounding the "CONCATENATE" function with the same
#VALUE error.

Anyone know a work-around or better way to achieve this?


--

Dave Peterson



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
How do I find the cell reference for a vlookup? Curious Excel User Excel Worksheet Functions 2 August 2nd 06 11:50 PM
How do I get the cell address of a VLOOKUP reference? tfleischny Excel Worksheet Functions 4 January 4th 06 11:16 PM
Using single cell reference as table array argument in Vlookup CornNiblet Excel Worksheet Functions 3 September 22nd 05 09:15 AM
Convert VLOOKUP to absolute cell reference Rich Excel Discussion (Misc queries) 2 August 6th 05 03:49 AM
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 1 December 2nd 04 08:30 PM


All times are GMT +1. The time now is 06:46 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017