Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name "Biz")
c4 - is having a list (thro' data-validation-,list range name "Region")
D5- is having a list (thro' data-validation-list range = formula as above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Could you explain a little bit further what you're trying to achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le message de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name "Biz")
c4 - is having a list (thro' data-validation-,list range name "Region")
D5- is having a list (thro' data-validation-list range = formula as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Hi
TigerEast is a range having units of tiger business units in the eastern
region
B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
C5 is to choose Regions North / South / East / West / All
B5 I have kept a list thro' data-Validation (criterial: List / Source: Biz
(list of buz.)
similarly C5 has list of regions.
So If business & region is selected then the units pertaining to that biz &
region should be available to choose at d5 (for d5 also I have the ranged
names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to use
indirect & counta to list the range with counta.
Listing is coming but it pulls one more unit from next range, ranges are one
below the other(for your information).

Thanks/ Eddy Stan

"Ardus Petus" wrote:

Could you explain a little bit further what you're trying to achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le message de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name "Biz")
c4 - is having a list (thro' data-validation-,list range name "Region")
D5- is having a list (thro' data-validation-list range = formula as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows 7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

I don't understand why you resize the "TigerEast" range.
Maybe that's because it may contain blank cells.
But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!

HTH
--
AP

"Eddy Stan" a écrit dans le message de
...
Hi
TigerEast is a range having units of tiger business units in the eastern
region
B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
C5 is to choose Regions North / South / East / West / All
B5 I have kept a list thro' data-Validation (criterial: List / Source:
Biz
(list of buz.)
similarly C5 has list of regions.
So If business & region is selected then the units pertaining to that biz
&
region should be available to choose at d5 (for d5 also I have the ranged
names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to use
indirect & counta to list the range with counta.
Listing is coming but it pulls one more unit from next range, ranges are
one
below the other(for your information).

Thanks/ Eddy Stan

"Ardus Petus" wrote:

Could you explain a little bit further what you're trying to achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le message
de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as
tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name "Biz")
c4 - is having a list (thro' data-validation-,list range name
"Region")
D5- is having a list (thro' data-validation-list range = formula as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows
7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Oh ... Thanks for the hint. I was misleaded by offset example I had.
I am getting the result takka tak by:
=indirect(vlookup(c5&c6,regionbizname,1,false))
Anyway I couldn't learn how to use offset !
I don't know how to use offset & what situation I need to use offset.


"Ardus Petus" wrote:

I don't understand why you resize the "TigerEast" range.
Maybe that's because it may contain blank cells.
But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!

HTH
--
AP

"Eddy Stan" a écrit dans le message de
...
Hi
TigerEast is a range having units of tiger business units in the eastern
region
B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
C5 is to choose Regions North / South / East / West / All
B5 I have kept a list thro' data-Validation (criterial: List / Source:
Biz
(list of buz.)
similarly C5 has list of regions.
So If business & region is selected then the units pertaining to that biz
&
region should be available to choose at d5 (for d5 also I have the ranged
names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to use
indirect & counta to list the range with counta.
Listing is coming but it pulls one more unit from next range, ranges are
one
below the other(for your information).

Thanks/ Eddy Stan

"Ardus Petus" wrote:

Could you explain a little bit further what you're trying to achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le message
de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as
tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name "Biz")
c4 - is having a list (thro' data-validation-,list range name
"Region")
D5- is having a list (thro' data-validation-list range = formula as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing 3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT shows
7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.

Thanks in advance, some one help me.

Did uou have a look at HELP on OFFSET functiçon?

--
AP

"Eddy Stan" a écrit dans le message de
...
Oh ... Thanks for the hint. I was misleaded by offset example I had.
I am getting the result takka tak by:
=indirect(vlookup(c5&c6,regionbizname,1,false))
Anyway I couldn't learn how to use offset !
I don't know how to use offset & what situation I need to use offset.


"Ardus Petus" wrote:

I don't understand why you resize the "TigerEast" range.
Maybe that's because it may contain blank cells.
But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!

HTH
--
AP

"Eddy Stan" a écrit dans le message
de
...
Hi
TigerEast is a range having units of tiger business units in the
eastern
region
B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
C5 is to choose Regions North / South / East / West / All
B5 I have kept a list thro' data-Validation (criterial: List /
Source:
Biz
(list of buz.)
similarly C5 has list of regions.
So If business & region is selected then the units pertaining to that
biz
&
region should be available to choose at d5 (for d5 also I have the
ranged
names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to
use
indirect & counta to list the range with counta.
Listing is coming but it pulls one more unit from next range, ranges
are
one
below the other(for your information).

Thanks/ Eddy Stan

"Ardus Petus" wrote:

Could you explain a little bit further what you're trying to
achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le
message
de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as
tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name
"Biz")
c4 - is having a list (thro' data-validation-,list range name
"Region")
D5- is having a list (thro' data-validation-list range = formula
as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing
3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT
shows
7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no
results.

Thanks in advance, some one help me.

Yes. I saw but can that be used in validation.
For example: for a2 & b2 (biz type & region) I need a pull down list at c2
(using data-validation-List option). How should be my offset function ? How
should be my refering array ? I put lot of time but no use boss, so quit
offset & used vlookup.
if you have any example give me please...

"Ardus Petus" wrote:

Did uou have a look at HELP on OFFSET functiçon?

--
AP

"Eddy Stan" a écrit dans le message de
...
Oh ... Thanks for the hint. I was misleaded by offset example I had.
I am getting the result takka tak by:
=indirect(vlookup(c5&c6,regionbizname,1,false))
Anyway I couldn't learn how to use offset !
I don't know how to use offset & what situation I need to use offset.


"Ardus Petus" wrote:

I don't understand why you resize the "TigerEast" range.
Maybe that's because it may contain blank cells.
But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!

HTH
--
AP

"Eddy Stan" a écrit dans le message
de
...
Hi
TigerEast is a range having units of tiger business units in the
eastern
region
B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
C5 is to choose Regions North / South / East / West / All
B5 I have kept a list thro' data-Validation (criterial: List /
Source:
Biz
(list of buz.)
similarly C5 has list of regions.
So If business & region is selected then the units pertaining to that
biz
&
region should be available to choose at d5 (for d5 also I have the
ranged
names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to
use
indirect & counta to list the range with counta.
Listing is coming but it pulls one more unit from next range, ranges
are
one
below the other(for your information).

Thanks/ Eddy Stan

"Ardus Petus" wrote:

Could you explain a little bit further what you're trying to
achieve?

Is TigerEast a named range?

What do you have in B5 and C5?

Cheers,
--
AP

"Eddy Stan" a écrit dans le
message
de
...
Hi - Can anyone identify & rectify the error
I gave this formula at Data-validation-List ; Source
results shows first item of next range also.
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
N2 = B5&C5 (that is TigerEast; I have defined a range name as
tigereast)

B4 C4 D4
Biz Region Branch
Tiger East Haldia Branch

B4 - is having a list (thro' data-validation-list range name
"Biz")
c4 - is having a list (thro' data-validation-,list range name
"Region")
D5- is having a list (thro' data-validation-list range = formula
as
above

Requirement:
When Tiger & East is chosen, then 2 units have to show BUT showing
3
units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger & South is chosen, then 6 units have to be shown BUT
shows
7
units, that is one more unit of next range (tigereast)
I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
=OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no
results.

Thanks in advance, some one help me.