| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDIRECT(ADDRESS) inside a VLOOKUP
F2/Enter didn't work, neither did recalc....
To your formula ideas below, both of those options worked. With the file open. But neither worked when the target was closed. "Dave Peterson" wrote: 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) Dave Peterson |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDIRECT(ADDRESS) inside a VLOOKUP
Sorry, I don't use =indirect.ext() enough to help debug your problem.
Maybe someone else will jump in. JG wrote: F2/Enter didn't work, neither did recalc.... To your formula ideas below, both of those options worked. With the file open. But neither worked when the target was closed. "Dave Peterson" wrote: 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) Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDIRECT(ADDRESS) inside a VLOOKUP
JG wrote...
.... =VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)), INDIRECT("[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2) .... Note: the external reference above needs to start off as either '[Filename.xls]MainQueryResult'!$B$2: or [Filename.xls]MainQueryResult!$B$2: but not as you show it above. IOW, the single quotes need to enclose the filename portion of the reference as well as the worksheet name, or you need to dispense with them. Wrapping only the worksheet names in single quotes is a reference syntax error, so Excel would CORRECTLY return #REF!. This isn't the best way to handle a problem like this. There's no good reason to use the first INDIRECT call. If you enter this formula in cell X99, you'd be better off replacing the first INDIRECT call with $A99. If you need to protect against range insertion/deletion moving cells in column A, replace the first INDIRECT call with INDEX($A:$A,SUM (ROW())). [SUM(ROW()) is intentional and protects against very subtle bugs that can arise because ROW always returns an array, even single results as single value arrays, and that can cause problems. Wrapping ROW() inside SUM converts the result to a scalar value.] Since you're using a sorted lookup, you could check whether your lookup value from col A would be found by the Count_Comp!A2 row of the external table, and only if so perform a lookup. Otherwise, just return the value at the Count_Comp!A2 row. =INDEX('[Filename.xls]MainQueryResult'!$B$2:$D$65536, IF(INDEX($A:$A,SUM(ROW()))<INDEX('[Filename.xls]MainQueryResult'!$B $2:$B$65536,Count_Comp!$A$2), MATCH(INDEX($A:$A,SUM(ROW())),'[Filename.xls]MainQueryResult'!$B$2:$B $65536),Count_Comp!$A$2)) This should recalc more quickly. It also avoids volatile functions. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
INDIRECT(ADDRESS) inside a VLOOKUP
Thanks, I appreciate the time.
"Dave Peterson" wrote: Sorry, I don't use =indirect.ext() enough to help debug your problem. Maybe someone else will jump in. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| INDIRECT and ADDRESS | Excel Worksheet Functions | |||
| Indirect(NamedRange) inside Sumproduct | Excel Worksheet Functions | |||
| Indirect inside a vlookup | Excel Worksheet Functions | |||
| Indirect address inside "" | Setting up and Configuration of Excel | |||
Linear Mode
