Matt Miller
asked on
2 Questions Involving Lookups for An Array in a different workbook
Hello,
I am working with multiples trying to automate an end of a month progress. And a couple questions have arisen that I can't seem to solve.
This formula works to pull the appropriate value
=VLOOKUP(VLOOKUP($A:$A,'Ke y to 13 Month'!$A:$B,2,FALSE),'Q:\ 1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N $65536,14, FALSE)
And this formula works.
{=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N $65536,{7, 8,9,10,11, 12,13,14}, FALSE))}
1)However, I can't seem to turn that second formula into a Vlookup from a hard code. Is it possible or does it require hard coding?
2)Is there a way to turn the array into a cell reference? {7,8,9,10,11,12,13,14} I've tried with no success.
I am working with multiples trying to automate an end of a month progress. And a couple questions have arisen that I can't seem to solve.
This formula works to pull the appropriate value
=VLOOKUP(VLOOKUP($A:$A,'Ke
And this formula works.
{=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N
1)However, I can't seem to turn that second formula into a Vlookup from a hard code. Is it possible or does it require hard coding?
2)Is there a way to turn the array into a cell reference? {7,8,9,10,11,12,13,14} I've tried with no success.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in that case, that may need the dynamic range.
are you familiar of creating dynamic expandable range?
are you familiar of creating dynamic expandable range?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am not familiar with dynamic expandable ranges.
Glenn, I think your column G:N thing might be the solution I'm looking for. Is there a way to turn that G:N into referencing a cell value? I'm experimenting with it with no luck so far.
Glenn, I think your column G:N thing might be the solution I'm looking for. Is there a way to turn that G:N into referencing a cell value? I'm experimenting with it with no luck so far.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. That's exactly what I was looking for.
ASKER
The array works if I have the same number of variables but breaks if I have less or more. Is there a way can I can reference a particular cell to do that?
Thank you for your help.