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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|