INDIRECT

[Guest]

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.

 

[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

 

[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

 

[Guest]

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.

 

[Guest]

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.

 

[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

 

[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

 

[Guest]

Works perfectly. Thanks so much.
--
Andrew

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

 

[Guest]

Works perfectly. Thanks so much.
--
Andrew

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