software

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

B4 C4 D4
BizRegionBranch
TigerEastHaldia Branch

B4 - is having a list (thro' data-gt;validation-gt;list range name quot;Bizquot;)
c4 - is having a list (thro' data-gt;validation-gt;,list range name quot;Regionquot;)
D5- is having a list (thro' data-gt;validation-gt;list range = formula as above

Requirement:
When Tiger amp; East is chosen, then 2 units have to show BUT showing 3 units,
actually the 3rd unit belongs to next range (tigerwest)
When Tiger amp; 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

quot;Eddy Stanquot; gt; a écrit dans le message de
...
gt; Hi - Can anyone identify amp; rectify the error
gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; results shows first item of next range also.
gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as tigereast)
gt;
gt; B4 C4 D4
gt; Biz Region Branch
gt; Tiger East Haldia Branch
gt;
gt; B4 - is having a list (thro' data-gt;validation-gt;list range name quot;Bizquot;)
gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name quot;Regionquot;)
gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula as
above
gt;
gt; Requirement:
gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing 3
units,
gt; actually the 3rd unit belongs to next range (tigerwest)
gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT shows 7
gt; units, that is one more unit of next range (tigereast)
gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.
gt;
gt; Thanks in advance, some one help me.
gt;
gt;
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-gt;Validation (criterial: List / Source: Biz
(list of buz.)
similarly C5 has list of regions.
So If business amp; region is selected then the units pertaining to that biz amp;
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 amp; 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

quot;Ardus Petusquot; wrote:

gt; Could you explain a little bit further what you're trying to achieve?
gt;
gt; Is TigerEast a named range?
gt;
gt; What do you have in B5 and C5?
gt;
gt; Cheers,
gt; --
gt; AP
gt;
gt; quot;Eddy Stanquot; gt; a écrit dans le message de
gt; ...
gt; gt; Hi - Can anyone identify amp; rectify the error
gt; gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; gt; results shows first item of next range also.
gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as tigereast)
gt; gt;
gt; gt; B4 C4 D4
gt; gt; Biz Region Branch
gt; gt; Tiger East Haldia Branch
gt; gt;
gt; gt; B4 - is having a list (thro' data-gt;validation-gt;list range name quot;Bizquot;)
gt; gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name quot;Regionquot;)
gt; gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula as
gt; above
gt; gt;
gt; gt; Requirement:
gt; gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing 3
gt; units,
gt; gt; actually the 3rd unit belongs to next range (tigerwest)
gt; gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT shows 7
gt; gt; units, that is one more unit of next range (tigereast)
gt; gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.
gt; gt;
gt; gt; Thanks in advance, some one help me.
gt; gt;
gt; gt;
gt;
gt;
gt;

I don't understand why you resize the quot;TigerEastquot; 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

quot;Eddy Stanquot; gt; a écrit dans le message de
...
gt; Hi
gt; TigerEast is a range having units of tiger business units in the eastern
gt; region
gt; B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
gt; C5 is to choose Regions North / South / East / West / All
gt; B5 I have kept a list thro' data-gt;Validation (criterial: List / Source:
Biz
gt; (list of buz.)
gt; similarly C5 has list of regions.
gt; So If business amp; region is selected then the units pertaining to that biz
amp;
gt; region should be available to choose at d5 (for d5 also I have the ranged
gt; names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to use
gt; indirect amp; counta to list the range with counta.
gt; Listing is coming but it pulls one more unit from next range, ranges are
one
gt; below the other(for your information).
gt;
gt; Thanks/ Eddy Stan
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; Could you explain a little bit further what you're trying to achieve?
gt; gt;
gt; gt; Is TigerEast a named range?
gt; gt;
gt; gt; What do you have in B5 and C5?
gt; gt;
gt; gt; Cheers,
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Eddy Stanquot; gt; a écrit dans le message
de
gt; gt; ...
gt; gt; gt; Hi - Can anyone identify amp; rectify the error
gt; gt; gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; gt; gt; results shows first item of next range also.
gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; gt; gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as
tigereast)
gt; gt; gt;
gt; gt; gt; B4 C4 D4
gt; gt; gt; Biz Region Branch
gt; gt; gt; Tiger East Haldia Branch
gt; gt; gt;
gt; gt; gt; B4 - is having a list (thro' data-gt;validation-gt;list range name quot;Bizquot;)
gt; gt; gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name
quot;Regionquot;)
gt; gt; gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula as
gt; gt; above
gt; gt; gt;
gt; gt; gt; Requirement:
gt; gt; gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing 3
gt; gt; units,
gt; gt; gt; actually the 3rd unit belongs to next range (tigerwest)
gt; gt; gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT shows
7
gt; gt; gt; units, that is one more unit of next range (tigereast)
gt; gt; gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.
gt; gt; gt;
gt; gt; gt; Thanks in advance, some one help me.
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Oh ... Thanks for the hint. I was misleaded by offset example I had.
I am getting the result takka tak by:
=indirect(vlookup(c5amp;c6,regionbizname,1,false))
Anyway I couldn't learn how to use offset !
I don't know how to use offset amp; what situation I need to use offset.quot;Ardus Petusquot; wrote:

gt; I don't understand why you resize the quot;TigerEastquot; range.
gt; Maybe that's because it may contain blank cells.
gt; But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Eddy Stanquot; gt; a écrit dans le message de
gt; ...
gt; gt; Hi
gt; gt; TigerEast is a range having units of tiger business units in the eastern
gt; gt; region
gt; gt; B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
gt; gt; C5 is to choose Regions North / South / East / West / All
gt; gt; B5 I have kept a list thro' data-gt;Validation (criterial: List / Source:
gt; Biz
gt; gt; (list of buz.)
gt; gt; similarly C5 has list of regions.
gt; gt; So If business amp; region is selected then the units pertaining to that biz
gt; amp;
gt; gt; region should be available to choose at d5 (for d5 also I have the ranged
gt; gt; names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to use
gt; gt; indirect amp; counta to list the range with counta.
gt; gt; Listing is coming but it pulls one more unit from next range, ranges are
gt; one
gt; gt; below the other(for your information).
gt; gt;
gt; gt; Thanks/ Eddy Stan
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; Could you explain a little bit further what you're trying to achieve?
gt; gt; gt;
gt; gt; gt; Is TigerEast a named range?
gt; gt; gt;
gt; gt; gt; What do you have in B5 and C5?
gt; gt; gt;
gt; gt; gt; Cheers,
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Eddy Stanquot; gt; a écrit dans le message
gt; de
gt; gt; gt; ...
gt; gt; gt; gt; Hi - Can anyone identify amp; rectify the error
gt; gt; gt; gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; gt; gt; gt; results shows first item of next range also.
gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; gt; gt; gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as
gt; tigereast)
gt; gt; gt; gt;
gt; gt; gt; gt; B4 C4 D4
gt; gt; gt; gt; Biz Region Branch
gt; gt; gt; gt; Tiger East Haldia Branch
gt; gt; gt; gt;
gt; gt; gt; gt; B4 - is having a list (thro' data-gt;validation-gt;list range name quot;Bizquot;)
gt; gt; gt; gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name
gt; quot;Regionquot;)
gt; gt; gt; gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula as
gt; gt; gt; above
gt; gt; gt; gt;
gt; gt; gt; gt; Requirement:
gt; gt; gt; gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing 3
gt; gt; gt; units,
gt; gt; gt; gt; actually the 3rd unit belongs to next range (tigerwest)
gt; gt; gt; gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT shows
gt; 7
gt; gt; gt; gt; units, that is one more unit of next range (tigereast)
gt; gt; gt; gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no results.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance, some one help me.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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

--
AP

quot;Eddy Stanquot; gt; a écrit dans le message de
...
gt; Oh ... Thanks for the hint. I was misleaded by offset example I had.
gt; I am getting the result takka tak by:
gt; =indirect(vlookup(c5amp;c6,regionbizname,1,false))
gt; Anyway I couldn't learn how to use offset !
gt; I don't know how to use offset amp; what situation I need to use offset.
gt;
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; I don't understand why you resize the quot;TigerEastquot; range.
gt; gt; Maybe that's because it may contain blank cells.
gt; gt; But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Eddy Stanquot; gt; a écrit dans le message
de
gt; gt; ...
gt; gt; gt; Hi
gt; gt; gt; TigerEast is a range having units of tiger business units in the
eastern
gt; gt; gt; region
gt; gt; gt; B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
gt; gt; gt; C5 is to choose Regions North / South / East / West / All
gt; gt; gt; B5 I have kept a list thro' data-gt;Validation (criterial: List /
Source:
gt; gt; Biz
gt; gt; gt; (list of buz.)
gt; gt; gt; similarly C5 has list of regions.
gt; gt; gt; So If business amp; region is selected then the units pertaining to that
biz
gt; gt; amp;
gt; gt; gt; region should be available to choose at d5 (for d5 also I have the
ranged
gt; gt; gt; names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to
use
gt; gt; gt; indirect amp; counta to list the range with counta.
gt; gt; gt; Listing is coming but it pulls one more unit from next range, ranges
are
gt; gt; one
gt; gt; gt; below the other(for your information).
gt; gt; gt;
gt; gt; gt; Thanks/ Eddy Stan
gt; gt; gt;
gt; gt; gt; quot;Ardus Petusquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Could you explain a little bit further what you're trying to
achieve?
gt; gt; gt; gt;
gt; gt; gt; gt; Is TigerEast a named range?
gt; gt; gt; gt;
gt; gt; gt; gt; What do you have in B5 and C5?
gt; gt; gt; gt;
gt; gt; gt; gt; Cheers,
gt; gt; gt; gt; --
gt; gt; gt; gt; AP
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Eddy Stanquot; gt; a écrit dans le
message
gt; gt; de
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi - Can anyone identify amp; rectify the error
gt; gt; gt; gt; gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; gt; gt; gt; gt; results shows first item of next range also.
gt; gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; gt; gt; gt; gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as
gt; gt; tigereast)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; B4 C4 D4
gt; gt; gt; gt; gt; Biz Region Branch
gt; gt; gt; gt; gt; Tiger East Haldia Branch
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; B4 - is having a list (thro' data-gt;validation-gt;list range name
quot;Bizquot;)
gt; gt; gt; gt; gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name
gt; gt; quot;Regionquot;)
gt; gt; gt; gt; gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula
as
gt; gt; gt; gt; above
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Requirement:
gt; gt; gt; gt; gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing
3
gt; gt; gt; gt; units,
gt; gt; gt; gt; gt; actually the 3rd unit belongs to next range (tigerwest)
gt; gt; gt; gt; gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT
shows
gt; gt; 7
gt; gt; gt; gt; gt; units, that is one more unit of next range (tigereast)
gt; gt; gt; gt; gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no
results.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks in advance, some one help me.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
Yes. I saw but can that be used in validation.
For example: for a2 amp; b2 (biz type amp; region) I need a pull down list at c2
(using data-gt;validation-gt;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 amp; used vlookup.
if you have any example give me please...

quot;Ardus Petusquot; wrote:

gt; Did uou have a look at HELP on OFFSET functiçon?
gt;
gt; --
gt; AP
gt;
gt; quot;Eddy Stanquot; gt; a écrit dans le message de
gt; ...
gt; gt; Oh ... Thanks for the hint. I was misleaded by offset example I had.
gt; gt; I am getting the result takka tak by:
gt; gt; =indirect(vlookup(c5amp;c6,regionbizname,1,false))
gt; gt; Anyway I couldn't learn how to use offset !
gt; gt; I don't know how to use offset amp; what situation I need to use offset.
gt; gt;
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; I don't understand why you resize the quot;TigerEastquot; range.
gt; gt; gt; Maybe that's because it may contain blank cells.
gt; gt; gt; But the OFFSET(rng,0,0,COUNTA(rng)),1) won't suppress these blank cells!
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Eddy Stanquot; gt; a écrit dans le message
gt; de
gt; gt; gt; ...
gt; gt; gt; gt; Hi
gt; gt; gt; gt; TigerEast is a range having units of tiger business units in the
gt; eastern
gt; gt; gt; gt; region
gt; gt; gt; gt; B5 is to choose Business Tiger / Cheetah / Falcon / Puma / All
gt; gt; gt; gt; C5 is to choose Regions North / South / East / West / All
gt; gt; gt; gt; B5 I have kept a list thro' data-gt;Validation (criterial: List /
gt; Source:
gt; gt; gt; Biz
gt; gt; gt; gt; (list of buz.)
gt; gt; gt; gt; similarly C5 has list of regions.
gt; gt; gt; gt; So If business amp; region is selected then the units pertaining to that
gt; biz
gt; gt; gt; amp;
gt; gt; gt; gt; region should be available to choose at d5 (for d5 also I have the
gt; ranged
gt; gt; gt; gt; names like TigerEast, TigerSouth, TigerNorth, so on. I am trying to
gt; use
gt; gt; gt; gt; indirect amp; counta to list the range with counta.
gt; gt; gt; gt; Listing is coming but it pulls one more unit from next range, ranges
gt; are
gt; gt; gt; one
gt; gt; gt; gt; below the other(for your information).
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks/ Eddy Stan
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ardus Petusquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Could you explain a little bit further what you're trying to
gt; achieve?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is TigerEast a named range?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; What do you have in B5 and C5?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Cheers,
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; AP
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Eddy Stanquot; gt; a écrit dans le
gt; message
gt; gt; gt; de
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; Hi - Can anyone identify amp; rectify the error
gt; gt; gt; gt; gt; gt; I gave this formula at Data-gt;validation-gt;List ; Source
gt; gt; gt; gt; gt; gt; results shows first item of next range also.
gt; gt; gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2),1))
gt; gt; gt; gt; gt; gt; N2 = B5amp;C5 (that is TigerEast; I have defined a range name as
gt; gt; gt; tigereast)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; B4 C4 D4
gt; gt; gt; gt; gt; gt; Biz Region Branch
gt; gt; gt; gt; gt; gt; Tiger East Haldia Branch
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; B4 - is having a list (thro' data-gt;validation-gt;list range name
gt; quot;Bizquot;)
gt; gt; gt; gt; gt; gt; c4 - is having a list (thro' data-gt;validation-gt;,list range name
gt; gt; gt; quot;Regionquot;)
gt; gt; gt; gt; gt; gt; D5- is having a list (thro' data-gt;validation-gt;list range = formula
gt; as
gt; gt; gt; gt; gt; above
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Requirement:
gt; gt; gt; gt; gt; gt; When Tiger amp; East is chosen, then 2 units have to show BUT showing
gt; 3
gt; gt; gt; gt; gt; units,
gt; gt; gt; gt; gt; gt; actually the 3rd unit belongs to next range (tigerwest)
gt; gt; gt; gt; gt; gt; When Tiger amp; South is chosen, then 6 units have to be shown BUT
gt; shows
gt; gt; gt; 7
gt; gt; gt; gt; gt; gt; units, that is one more unit of next range (tigereast)
gt; gt; gt; gt; gt; gt; I tried as =OFFSET(INDIRECT(N2),-1,0,COUNTA(INDIRECT($N$2),1)) and
gt; gt; gt; gt; gt; gt; =OFFSET(INDIRECT(N2),0,0,COUNTA(INDIRECT($N$2)-1,1)); but no
gt; results.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks in advance, some one help me.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;