Prev: Variable Length Columns
Next: Need to add semi colon in time numbers
From: WembleyBear on 29 Apr 2010 14:31
I have a vlookup which looks up the value of an expense code for a particular
branch, set up as a named range (BRANCH A) & chosen from a drop-down list in
cell C3. It works fine for one expense code 4001 thus:

=VLOOKUP(4001,INDIRECT(C3),2,FALSE)

My problem comes when in some instances I need to add together the value of
several (up to 10) expense codes in that cell, say 4001,4003,4011,4014). The
total value would then change dependent on which branch is selected, but the
group of expense codes would always be the same irrespective of which branch
is chosen in C3. What is the easiest way of achieving this?


Thanks
Martyn

--
Excel 2000, Windows 2003 over Citrix PS4
From: Luke M on 29 Apr 2010 15:15
Easiest? Probably just:
=VLOOKUP(4001,INDIRECT(C3),2,FALSE)+
VLOOKUP(4003,INDIRECT(C3),2,FALSE)+
VLOOKUP(4011,INDIRECT(C3),2,FALSE)+
VLOOKUP(4014,INDIRECT(C3),2,FALSE)





--
Best Regards,

Luke M
"WembleyBear" <WembleyBear(a)discussions.microsoft.com> wrote in message
news:F4F83FB0-91CE-4505-954D-EAB8D05D6782(a)microsoft.com...
>I have a vlookup which looks up the value of an expense code for a
>particular
> branch, set up as a named range (BRANCH A) & chosen from a drop-down list
> in
> cell C3. It works fine for one expense code 4001 thus:
>
> =VLOOKUP(4001,INDIRECT(C3),2,FALSE)
>
> My problem comes when in some instances I need to add together the value
> of
> several (up to 10) expense codes in that cell, say 4001,4003,4011,4014).
> The
> total value would then change dependent on which branch is selected, but
> the
> group of expense codes would always be the same irrespective of which
> branch
> is chosen in C3. What is the easiest way of achieving this?
>
>
> Thanks
> Martyn
>
> --
> Excel 2000, Windows 2003 over Citrix PS4


From: WembleyBear on 29 Apr 2010 16:21
Yes, I had thought of that but several of the groups of codes would produce a
formula that is too long if I used that method.


--
Excel 2000, Windows 2003 over Citrix PS4


"Luke M" wrote:

> Easiest? Probably just:
> =VLOOKUP(4001,INDIRECT(C3),2,FALSE)+
> VLOOKUP(4003,INDIRECT(C3),2,FALSE)+
> VLOOKUP(4011,INDIRECT(C3),2,FALSE)+
> VLOOKUP(4014,INDIRECT(C3),2,FALSE)
>
>
>
>
>
> --
> Best Regards,
>
> Luke M
> "WembleyBear" <WembleyBear(a)discussions.microsoft.com> wrote in message
> news:F4F83FB0-91CE-4505-954D-EAB8D05D6782(a)microsoft.com...
> >I have a vlookup which looks up the value of an expense code for a
> >particular
> > branch, set up as a named range (BRANCH A) & chosen from a drop-down list
> > in
> > cell C3. It works fine for one expense code 4001 thus:
> >
> > =VLOOKUP(4001,INDIRECT(C3),2,FALSE)
> >
> > My problem comes when in some instances I need to add together the value
> > of
> > several (up to 10) expense codes in that cell, say 4001,4003,4011,4014).
> > The
> > total value would then change dependent on which branch is selected, but
> > the
> > group of expense codes would always be the same irrespective of which
> > branch
> > is chosen in C3. What is the easiest way of achieving this?
> >
> >
> > Thanks
> > Martyn
> >
> > --
> > Excel 2000, Windows 2003 over Citrix PS4
>
>
> .
>
From: T. Valko on 29 Apr 2010 22:12
Not sure I understand your layout...

BRANCH A is not a valid range name. Can't include spaces.

If you have a lookup table like this named BRANCH_A:

4000...10
4001...22
4002...17
4003...15

And the table is sorted in ascending like above *and* there will always be
an exact match of the lookup values, then try something like this:

A1 = 4001
A2 = 4003

=SUMPRODUCT(LOOKUP(A1:A2,INDIRECT(C3)))

Result = 37

--
Biff
Microsoft Excel MVP


"WembleyBear" <WembleyBear(a)discussions.microsoft.com> wrote in message
news:67AD34CA-376B-4393-B17F-F6264997640D(a)microsoft.com...
> Yes, I had thought of that but several of the groups of codes would
> produce a
> formula that is too long if I used that method.
>
>
> --
> Excel 2000, Windows 2003 over Citrix PS4
>
>
> "Luke M" wrote:
>
>> Easiest? Probably just:
>> =VLOOKUP(4001,INDIRECT(C3),2,FALSE)+
>> VLOOKUP(4003,INDIRECT(C3),2,FALSE)+
>> VLOOKUP(4011,INDIRECT(C3),2,FALSE)+
>> VLOOKUP(4014,INDIRECT(C3),2,FALSE)
>>
>>
>>
>>
>>
>> --
>> Best Regards,
>>
>> Luke M
>> "WembleyBear" <WembleyBear(a)discussions.microsoft.com> wrote in message
>> news:F4F83FB0-91CE-4505-954D-EAB8D05D6782(a)microsoft.com...
>> >I have a vlookup which looks up the value of an expense code for a
>> >particular
>> > branch, set up as a named range (BRANCH A) & chosen from a drop-down
>> > list
>> > in
>> > cell C3. It works fine for one expense code 4001 thus:
>> >
>> > =VLOOKUP(4001,INDIRECT(C3),2,FALSE)
>> >
>> > My problem comes when in some instances I need to add together the
>> > value
>> > of
>> > several (up to 10) expense codes in that cell, say
>> > 4001,4003,4011,4014).
>> > The
>> > total value would then change dependent on which branch is selected,
>> > but
>> > the
>> > group of expense codes would always be the same irrespective of which
>> > branch
>> > is chosen in C3. What is the easiest way of achieving this?
>> >
>> >
>> > Thanks
>> > Martyn
>> >
>> > --
>> > Excel 2000, Windows 2003 over Citrix PS4
>>
>>
>> .
>>


 | 
Pages: 1
Prev: Variable Length Columns
Next: Need to add semi colon in time numbers