INDIRECT

[Rioville]

Hello folks,

I am quite confused by the behaviour of a INDIRECT formulae I created - a
rather simple one that that returns a listing of text from a sheet called
"customer list" (within the same workbook), depending on the result of a
Vlookup formula. The the VLOOKUP returns a range, say A50:A54, and
concatenating this with a sheet name 'customer list'! gives the range
reference INDIRECT needs.

My formulae as follows:

=INDIRECT(CONCATENATE("'customer list'!",VLOOKUP(Front!$C$12,'2006
MCs'!$B$2:$L$21,11,FALSE)))

I filled in this formula in cells A4 to A55 and was rather puzzled to see
that the cells A4 to A49 had #VALUE!, but Cells A50 to A54 had the desired
answers. Is it possible that the cells A4 always returns the first name in
the range, A5 the second name and so on.

Anything to clarify this welcome.
--
Andrew

[Arvi Laanemets]

Hi

You are throughly wrong here!

1. VLOOKP never doesn't return any range. It returns a single value from a
range - the first one matching the criteria.
=VLOOKUP(SearchValue,LookupRange,ReturnCol,SearchNearest)
SearchValue is the value the lookup us looking for in 1st column of
LookupRange;
LookupRange is the contignous range with search values column as
leftmost, and it includes column with return values;
ReturnColumn is an integer, which determines the relative position of
return values column in LookupRange - search column number is 1;
SearchNearest determines the behavior of VLOOKUP. When FALSE or 0, first
exact match is searched for - when not found, an error is retutrned. Lookup
range can be unsorted. When TRUE or 1, first nearest match is searched for.
Lookup range must be sorted on search column - otherwise wrong result may be
returned.

2. INDIRECT returns a range reference, which can be used as range parameter
on other functions. Indirect uses a single string parameter, which can be
read from some cell, or be calculated or entered directly. INDIRECT as
single or top-level function doesn't return anything visible - except the
return range is a single cell. Some examples.
=SUM(INDIRECT("Sheet1!A1:A10"))
It's same as =SUM(Sheet1!A1:A10);
=SUM(INDIRECT("'" & $A$1 & "!'A1:A10"))
It returns a sum of sells A1:A10 from sheet, which name is typed into
cell A1 on sheet with formula;
=INDIRECT("A1")
It's same as =A1, and returns a value from cell A1 - because the renge
returned by INDIRECT is a single cell.

Maybe you start with telling us what do you want to get, and from which
data. Then maybe somebody here gives you some solution.


Arvi Laanemets


"Rioville" <[email protected]> wrote in message
news:[email protected]...
> Hello folks,
>
> I am quite confused by the behaviour of a INDIRECT formulae I created - a
> rather simple one that that returns a listing of text from a sheet called
> "customer list" (within the same workbook), depending on the result of a
> Vlookup formula. The the VLOOKUP returns a range, say A50:A54, and
> concatenating this with a sheet name 'customer list'! gives the range
> reference INDIRECT needs.
>
> My formulae as follows:
>
> =INDIRECT(CONCATENATE("'customer list'!",VLOOKUP(Front!$C$12,'2006
> MCs'!$B$2:$L$21,11,FALSE)))
>
> I filled in this formula in cells A4 to A55 and was rather puzzled to see
> that the cells A4 to A49 had #VALUE!, but Cells A50 to A54 had the desired
> answers. Is it possible that the cells A4 always returns the first name in
> the range, A5 the second name and so on.
>
> Anything to clarify this welcome.
> --
> Andrew

[Rioville]

Arvi,

I have two sheets in a workbook. First sheet (customer list) Column A
contains customer names , Column B contains the Region each customer belongs
to. All customer names belonging to a region are in contigous ranges. The
second sheet is where the formula needs to be.

I need a function will automatically extract the list of customer names when
I enter the region name, so if I enter "Germany" the formula needs to return
all customer names belonging to the Germany region. Ditto for other regions.
Each region has a different number of customers.

I attempted to do this by having VLOOKUP return a range (say, $A$4:$A$44),
and using INDIRECT (concatenated with the sheet name) to return the customer
names defined by the range. the INDIRECT formula is then copied down to fill
the range with the correct customer names. This works when the indirect
formula is in cells A4-A44, and also happens to reference the VLOOKUP range
A4 to A44, but when the VLOOKUP references return another range, say $A$45 to
$A$55 the indirect only works when filled in Cells A45 to A55.

Am I missing something here? If there is another way, I would be most happy
for your help.

Thank you for reading.

--
Andrew


"Arvi Laanemets" wrote:

> Hi
>
> You are throughly wrong here!
>
> 1. VLOOKP never doesn't return any range. It returns a single value from a
> range - the first one matching the criteria.
> =VLOOKUP(SearchValue,LookupRange,ReturnCol,SearchNearest)
> SearchValue is the value the lookup us looking for in 1st column of
> LookupRange;
> LookupRange is the contignous range with search values column as
> leftmost, and it includes column with return values;
> ReturnColumn is an integer, which determines the relative position of
> return values column in LookupRange - search column number is 1;
> SearchNearest determines the behavior of VLOOKUP. When FALSE or 0, first
> exact match is searched for - when not found, an error is retutrned. Lookup
> range can be unsorted. When TRUE or 1, first nearest match is searched for.
> Lookup range must be sorted on search column - otherwise wrong result may be
> returned.
>
> 2. INDIRECT returns a range reference, which can be used as range parameter
> on other functions. Indirect uses a single string parameter, which can be
> read from some cell, or be calculated or entered directly. INDIRECT as
> single or top-level function doesn't return anything visible - except the
> return range is a single cell. Some examples.
> =SUM(INDIRECT("Sheet1!A1:A10"))
> It's same as =SUM(Sheet1!A1:A10);
> =SUM(INDIRECT("'" & $A$1 & "!'A1:A10"))
> It returns a sum of sells A1:A10 from sheet, which name is typed into
> cell A1 on sheet with formula;
> =INDIRECT("A1")
> It's same as =A1, and returns a value from cell A1 - because the renge
> returned by INDIRECT is a single cell.
>
> Maybe you start with telling us what do you want to get, and from which
> data. Then maybe somebody here gives you some solution.
>
>
> Arvi Laanemets
>
>
> "Rioville" <[email protected]> wrote in message
> news:[email protected]...
> > Hello folks,
> >
> > I am quite confused by the behaviour of a INDIRECT formulae I created - a
> > rather simple one that that returns a listing of text from a sheet called
> > "customer list" (within the same workbook), depending on the result of a
> > Vlookup formula. The the VLOOKUP returns a range, say A50:A54, and
> > concatenating this with a sheet name 'customer list'! gives the range
> > reference INDIRECT needs.
> >
> > My formulae as follows:
> >
> > =INDIRECT(CONCATENATE("'customer list'!",VLOOKUP(Front!$C$12,'2006
> > MCs'!$B$2:$L$21,11,FALSE)))
> >
> > I filled in this formula in cells A4 to A55 and was rather puzzled to see
> > that the cells A4 to A49 had #VALUE!, but Cells A50 to A54 had the desired
> > answers. Is it possible that the cells A4 always returns the first name in
> > the range, A5 the second name and so on.
> >
> > Anything to clarify this welcome.
> > --
> > Andrew
>
>
>

[Arvi Laanemets]

Hi

Let's start having a sheet Customers, with column headers in row 1
ID, Customer, Region (NB! I added a column ID, so customer names are now in
column B, and regions in column C)

Into A2 enter the formula
=IF(B2="","",C2&COUNTIF(C$2:C2,C2))
, and copy it down for some amount of rows (at least for entire table).

Define a named range
CustomersTbl=OFFSET(Customers!$A$1,1,,COUNTIF(Customers!$A:$A,">""")-1,3)

Create a sheet Regions
Into cell A2 enter the formula
=IF(A$1="","",IF(ISERROR(VLOOKUP(A$1&(ROW()-1),CustomersTbl,2,0)),"",VLOOKUP
(A$1&(ROW()-1),CustomersTbl,2,0)))
, and copy to right and down for as much columns you think you'll have
regions, and for as much rows you think max number of cistomers in any
region will be.

Now, whenever you enter region name into 1st row above range with formulas,
the full list of customers from this region is displayed in this column.

PS. On sheet Customers, you can hide the column ID now.


Arvi Laanemets


"Rioville" <[email protected]> wrote in message
news:[email protected]...
> Arvi,
>
> I have two sheets in a workbook. First sheet (customer list) Column A
> contains customer names , Column B contains the Region each customer
belongs
> to. All customer names belonging to a region are in contigous ranges. The
> second sheet is where the formula needs to be.
>
> I need a function will automatically extract the list of customer names
when
> I enter the region name, so if I enter "Germany" the formula needs to
return
> all customer names belonging to the Germany region. Ditto for other
regions.
> Each region has a different number of customers.
>
> I attempted to do this by having VLOOKUP return a range (say,
$A$4:$A$44),
> and using INDIRECT (concatenated with the sheet name) to return the
customer
> names defined by the range. the INDIRECT formula is then copied down to
fill
> the range with the correct customer names. This works when the indirect
> formula is in cells A4-A44, and also happens to reference the VLOOKUP
range
> A4 to A44, but when the VLOOKUP references return another range, say $A$45
to
> $A$55 the indirect only works when filled in Cells A45 to A55.
>
> Am I missing something here? If there is another way, I would be most
happy
> for your help.
>
> Thank you for reading.
>
> --
> Andrew
>
>
> "Arvi Laanemets" wrote:
>
> > Hi
> >
> > You are throughly wrong here!
> >
> > 1. VLOOKP never doesn't return any range. It returns a single value
from a
> > range - the first one matching the criteria.
> > =VLOOKUP(SearchValue,LookupRange,ReturnCol,SearchNearest)
> > SearchValue is the value the lookup us looking for in 1st column of
> > LookupRange;
> > LookupRange is the contignous range with search values column as
> > leftmost, and it includes column with return values;
> > ReturnColumn is an integer, which determines the relative position
of
> > return values column in LookupRange - search column number is 1;
> > SearchNearest determines the behavior of VLOOKUP. When FALSE or 0,
first
> > exact match is searched for - when not found, an error is retutrned.
Lookup
> > range can be unsorted. When TRUE or 1, first nearest match is searched
for.
> > Lookup range must be sorted on search column - otherwise wrong result
may be
> > returned.
> >
> > 2. INDIRECT returns a range reference, which can be used as range
parameter
> > on other functions. Indirect uses a single string parameter, which can
be
> > read from some cell, or be calculated or entered directly. INDIRECT as
> > single or top-level function doesn't return anything visible - except
the
> > return range is a single cell. Some examples.
> > =SUM(INDIRECT("Sheet1!A1:A10"))
> > It's same as =SUM(Sheet1!A1:A10);
> > =SUM(INDIRECT("'" & $A$1 & "!'A1:A10"))
> > It returns a sum of sells A1:A10 from sheet, which name is typed
into
> > cell A1 on sheet with formula;
> > =INDIRECT("A1")
> > It's same as =A1, and returns a value from cell A1 - because the
renge
> > returned by INDIRECT is a single cell.
> >
> > Maybe you start with telling us what do you want to get, and from which
> > data. Then maybe somebody here gives you some solution.
> >
> >
> > Arvi Laanemets
> >
> >
> > "Rioville" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello folks,
> > >
> > > I am quite confused by the behaviour of a INDIRECT formulae I
created - a
> > > rather simple one that that returns a listing of text from a sheet
called
> > > "customer list" (within the same workbook), depending on the result of
a
> > > Vlookup formula. The the VLOOKUP returns a range, say A50:A54, and
> > > concatenating this with a sheet name 'customer list'! gives the range
> > > reference INDIRECT needs.
> > >
> > > My formulae as follows:
> > >
> > > =INDIRECT(CONCATENATE("'customer list'!",VLOOKUP(Front!$C$12,'2006
> > > MCs'!$B$2:$L$21,11,FALSE)))
> > >
> > > I filled in this formula in cells A4 to A55 and was rather puzzled to
see
> > > that the cells A4 to A49 had #VALUE!, but Cells A50 to A54 had the
desired
> > > answers. Is it possible that the cells A4 always returns the first
name in
> > > the range, A5 the second name and so on.
> > >
> > > Anything to clarify this welcome.
> > > --
> > > Andrew
> >
> >
> >

[Rioville]

Works perfectly. Thanks so much.
--
Andrew


"Arvi Laanemets" wrote:

> Hi
>
> Let's start having a sheet Customers, with column headers in row 1
> ID, Customer, Region (NB! I added a column ID, so customer names are now in
> column B, and regions in column C)
>
> Into A2 enter the formula
> =IF(B2="","",C2&COUNTIF(C$2:C2,C2))
> , and copy it down for some amount of rows (at least for entire table).
>
> Define a named range
> CustomersTbl=OFFSET(Customers!$A$1,1,,COUNTIF(Customers!$A:$A,">""")-1,3)
>
> Create a sheet Regions
> Into cell A2 enter the formula
> =IF(A$1="","",IF(ISERROR(VLOOKUP(A$1&(ROW()-1),CustomersTbl,2,0)),"",VLOOKUP
> (A$1&(ROW()-1),CustomersTbl,2,0)))
> , and copy to right and down for as much columns you think you'll have
> regions, and for as much rows you think max number of cistomers in any
> region will be.
>
> Now, whenever you enter region name into 1st row above range with formulas,
> the full list of customers from this region is displayed in this column.
>
> PS. On sheet Customers, you can hide the column ID now.
>
>
> Arvi Laanemets
>
>
> "Rioville" <[email protected]> wrote in message
> news:[email protected]...
> > Arvi,
> >
> > I have two sheets in a workbook. First sheet (customer list) Column A
> > contains customer names , Column B contains the Region each customer
> belongs
> > to. All customer names belonging to a region are in contigous ranges. The
> > second sheet is where the formula needs to be.
> >
> > I need a function will automatically extract the list of customer names
> when
> > I enter the region name, so if I enter "Germany" the formula needs to
> return
> > all customer names belonging to the Germany region. Ditto for other
> regions.
> > Each region has a different number of customers.
> >
> > I attempted to do this by having VLOOKUP return a range (say,
> $A$4:$A$44),
> > and using INDIRECT (concatenated with the sheet name) to return the
> customer
> > names defined by the range. the INDIRECT formula is then copied down to
> fill
> > the range with the correct customer names. This works when the indirect
> > formula is in cells A4-A44, and also happens to reference the VLOOKUP
> range
> > A4 to A44, but when the VLOOKUP references return another range, say $A$45
> to
> > $A$55 the indirect only works when filled in Cells A45 to A55.
> >
> > Am I missing something here? If there is another way, I would be most
> happy
> > for your help.
> >
> > Thank you for reading.
> >
> > --
> > Andrew
> >
> >
> > "Arvi Laanemets" wrote:
> >
> > > Hi
> > >
> > > You are throughly wrong here!
> > >
> > > 1. VLOOKP never doesn't return any range. It returns a single value
> from a
> > > range - the first one matching the criteria.
> > > =VLOOKUP(SearchValue,LookupRange,ReturnCol,SearchNearest)
> > > SearchValue is the value the lookup us looking for in 1st column of
> > > LookupRange;
> > > LookupRange is the contignous range with search values column as
> > > leftmost, and it includes column with return values;
> > > ReturnColumn is an integer, which determines the relative position
> of
> > > return values column in LookupRange - search column number is 1;
> > > SearchNearest determines the behavior of VLOOKUP. When FALSE or 0,
> first
> > > exact match is searched for - when not found, an error is retutrned.
> Lookup
> > > range can be unsorted. When TRUE or 1, first nearest match is searched
> for.
> > > Lookup range must be sorted on search column - otherwise wrong result
> may be
> > > returned.
> > >
> > > 2. INDIRECT returns a range reference, which can be used as range
> parameter
> > > on other functions. Indirect uses a single string parameter, which can
> be
> > > read from some cell, or be calculated or entered directly. INDIRECT as
> > > single or top-level function doesn't return anything visible - except
> the
> > > return range is a single cell. Some examples.
> > > =SUM(INDIRECT("Sheet1!A1:A10"))
> > > It's same as =SUM(Sheet1!A1:A10);
> > > =SUM(INDIRECT("'" & $A$1 & "!'A1:A10"))
> > > It returns a sum of sells A1:A10 from sheet, which name is typed
> into
> > > cell A1 on sheet with formula;
> > > =INDIRECT("A1")
> > > It's same as =A1, and returns a value from cell A1 - because the
> renge
> > > returned by INDIRECT is a single cell.
> > >
> > > Maybe you start with telling us what do you want to get, and from which
> > > data. Then maybe somebody here gives you some solution.
> > >
> > >
> > > Arvi Laanemets
> > >
> > >
> > > "Rioville" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hello folks,
> > > >
> > > > I am quite confused by the behaviour of a INDIRECT formulae I
> created - a
> > > > rather simple one that that returns a listing of text from a sheet
> called
> > > > "customer list" (within the same workbook), depending on the result of
> a
> > > > Vlookup formula. The the VLOOKUP returns a range, say A50:A54, and
> > > > concatenating this with a sheet name 'customer list'! gives the range
> > > > reference INDIRECT needs.
> > > >
> > > > My formulae as follows:
> > > >
> > > > =INDIRECT(CONCATENATE("'customer list'!",VLOOKUP(Front!$C$12,'2006
> > > > MCs'!$B$2:$L$21,11,FALSE)))
> > > >
> > > > I filled in this formula in cells A4 to A55 and was rather puzzled to
> see
> > > > that the cells A4 to A49 had #VALUE!, but Cells A50 to A54 had the
> desired
> > > > answers. Is it possible that the cells A4 always returns the first
> name in
> > > > the range, A5 the second name and so on.
> > > >
> > > > Anything to clarify this welcome.
> > > > --
> > > > Andrew
> > >
> > >
> > >
>
>
>