A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

INDIRECT(ADDRESS) inside a VLOOKUP



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2009, 11:40 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default INDIRECT(ADDRESS) inside a VLOOKUP

Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
....etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.

  #2  
Old April 17th, 2009, 11:59 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default INDIRECT(ADDRESS) inside a VLOOKUP

Too much to read...

"JG" wrote:

Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
...etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.

  #3  
Old April 18th, 2009, 12:21 AM posted to microsoft.public.excel.worksheet.functions
Sheeloo
external usenet poster
 
Posts: 797
Default INDIRECT(ADDRESS) inside a VLOOKUP

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)

Second parameter has to be a reference... your formula is returning a
string... remember that the whole point of INDIRECT ... is to convert a
string to a reference... that is why you need to wrap your string building
formula with INDIRECT...

Also why are you not using A2 instead of INDIRECT(ADDRESS(ROW(), 1))? I
don't see any variable there other than row() which you any way get with 2 in
A2...

"JG" wrote:
Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
...etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.

  #4  
Old April 18th, 2009, 03:15 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default INDIRECT(ADDRESS) inside a VLOOKUP

That worked perfectly, thanks.

In regards to your question, I am not using A2 because I'm going to be
outputting over 1000 rows and the SQL is much more complex to output A2, A3,
A4...etc in the results than it is to use ADDRESS(row(), 1). My SQL will be:

select ID, '=vlookup(indirect(address(row(), 1)), .... +1), 3, )),2)' as
"NAME" from blah where....etc.

That will ensure that I am getting the correct address reference for each row.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)

Second parameter has to be a reference... your formula is returning a
string... remember that the whole point of INDIRECT ... is to convert a
string to a reference... that is why you need to wrap your string building
formula with INDIRECT...

Also why are you not using A2 instead of INDIRECT(ADDRESS(ROW(), 1))? I
don't see any variable there other than row() which you any way get with 2 in
A2...




"JG" wrote:
Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
...etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.

  #5  
Old April 18th, 2009, 06:16 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo
external usenet poster
 
Posts: 797
Default INDIRECT(ADDRESS) inside a VLOOKUP

Ok...

However won't it be easier to use something like "A"&i... not sure of the
syntax...

It might better if you have a large number of records...

"JG" wrote:

That worked perfectly, thanks.

In regards to your question, I am not using A2 because I'm going to be
outputting over 1000 rows and the SQL is much more complex to output A2, A3,
A4...etc in the results than it is to use ADDRESS(row(), 1). My SQL will be:

select ID, '=vlookup(indirect(address(row(), 1)), .... +1), 3, )),2)' as
"NAME" from blah where....etc.

That will ensure that I am getting the correct address reference for each row.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)

Second parameter has to be a reference... your formula is returning a
string... remember that the whole point of INDIRECT ... is to convert a
string to a reference... that is why you need to wrap your string building
formula with INDIRECT...

Also why are you not using A2 instead of INDIRECT(ADDRESS(ROW(), 1))? I
don't see any variable there other than row() which you any way get with 2 in
A2...




"JG" wrote:
Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
...etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.

  #6  
Old April 20th, 2009, 11:10 AM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default INDIRECT(ADDRESS) inside a VLOOKUP

Possibly... But this is easy enough to extrapolate from for my other columns.

Thanks again.

"Sheeloo" wrote:

Ok...

However won't it be easier to use something like "A"&i... not sure of the
syntax...

It might better if you have a large number of records...


  #7  
Old April 21st, 2009, 10:45 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default INDIRECT(ADDRESS) inside a VLOOKUP

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

....only to end up with #REF. Is this only going to work within the same file?

Thanks.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)


  #8  
Old April 22nd, 2009, 01:44 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default INDIRECT(ADDRESS) inside a VLOOKUP

First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.


JG wrote:

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)


--

Dave Peterson
  #9  
Old April 22nd, 2009, 05:07 PM posted to microsoft.public.excel.worksheet.functions
JG
external usenet poster
 
Posts: 75
Default INDIRECT(ADDRESS) inside a VLOOKUP

Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.
Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.

I have this formula in Work.xls which uses indirect.ext() by itself, and it
is pointing to a single cell reference:
=INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!$A$2")

This returns 1049, and I see that value whether Target File.xls is open or
closed.


In this formula, indirect.ext() is inside a vlookup:

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!$A$2:" &
ADDRESS((INDIRECT.EXT("'\\server\share\folder\[Target
File.xls]Count_TC'!$A$2")+1), 7)), 7)

I get a value returned just fine when Target File.xls is open, but not when
it's closed. Can I not use indirect.ext() to get data from a closed
spreadsheet to drive a vlookup()?

Oh, and =indirect("rc1",false) was cool, thanks for that.

"Dave Peterson" wrote:

First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.


JG wrote:

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)


--

Dave Peterson

  #10  
Old April 22nd, 2009, 05:44 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default INDIRECT(ADDRESS) inside a VLOOKUP

I think you're seeing that the formula hasn't reevaluated. With the sending
workbook closed, select that cell with the =indirect() formula and hit F2
followed by enter.

What happens?

===========
I don't use =indirect.ext(), but maybe...

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" &
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7)

(Untested.)

(I hardcoded the G into the formula instead of using =address(..., 7). And I
dropped the $ signs from the strings (since it's in a string, it won't matter).

One suggestion...

If the used range of that sending worksheet isn't too much (you'll have to test
it), maybe you can drop the count_tc stuff and just use the entire column.

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7)




JG wrote:

Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.
Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.

I have this formula in Work.xls which uses indirect.ext() by itself, and it
is pointing to a single cell reference:
=INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!$A$2")

This returns 1049, and I see that value whether Target File.xls is open or
closed.

In this formula, indirect.ext() is inside a vlookup:

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!$A$2:" &
ADDRESS((INDIRECT.EXT("'\\server\share\folder\[Target
File.xls]Count_TC'!$A$2")+1), 7)), 7)

I get a value returned just fine when Target File.xls is open, but not when
it's closed. Can I not use indirect.ext() to get data from a closed
spreadsheet to drive a vlookup()?

Oh, and =indirect("rc1",false) was cool, thanks for that.

"Dave Peterson" wrote:

First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.


JG wrote:

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)


--

Dave Peterson


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


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