 |
|
|
|
Next: Enable Combo box based on value in check box and ..
|
| Author |
Message |
External
Since: Dec 12, 2008
Posts: 6
|
(Msg. 1) Posted: Wed Oct 22, 2008 4:35 pm
Post subject: Using Named Range in LOOKUP formula?
Archived from groups: microsoft>public>excel (more info?)
|
|
|
My LOOKUP formula currently reads:
=((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
$7)))
I would like to change this so the ranges are columns in a Named
Range. Something like:
=((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
(LOOKUP($I6,Range_Col1,Range_Col7)))
The range name will be selected in a drop-down (data validation) in
another cell. So "Range_Col1" actually needs to be referenced from
the value selected in K10.
Can this be done with formulas? Or do I need a macro?
Ed |
|
| Back to top |
|
 | |
External
Since: Oct 22, 2008
Posts: 20
|
(Msg. 2) Posted: Wed Oct 22, 2008 10:15 pm
Post subject: Re: Using Named Range in LOOKUP formula?
Archived from groups: per prev. post (more info?)
|
|
|
Assuming your named range refers to A1:G7
You can use an expression like this:
INDEX(INDIRECT(K10),,column_number)
However, if the named range is a dynamic range this won't work.
=((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7)))) |
|
| Back to top |
|
| |
External
Since: Dec 12, 2008
Posts: 6
|
(Msg. 3) Posted: Thu Oct 23, 2008 10:56 am
Post subject: Re: Using Named Range in LOOKUP formula?
Archived from groups: per prev. post (more info?)
|
|
|
Perfect, Biff!! Thanks!!
Ed
On Oct 22, 7:15 pm, "T. Valko" wrote:
> Assuming your named range refers to A1:G7
>
> You can use an expression like this:
>
> INDEX(INDIRECT(K10),,column_number)
>
> However, if the named range is a dynamic range this won't work.
>
> =((LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,4))-J6)+(LOOKUP($I6,INDEX(INDIRECT(K10),,1),INDEX(INDIRECT(K10),,7))))
>
> --
> Biff
> Microsoft Excel MVP
>
> "Ed from AZ" wrote in message
>
>
>
> > My LOOKUP formula currently reads:
> > =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> > $7)))
>
> > I would like to change this so the ranges are columns in a Named
> > Range. Something like:
> > =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> > (LOOKUP($I6,Range_Col1,Range_Col7)))
>
> > The range name will be selected in a drop-down (data validation) in
> > another cell. So "Range_Col1" actually needs to be referenced from
> > the value selected in K10.
>
> > Can this be done with formulas? Or do I need a macro?
>
> > Ed- Hide quoted text -
>
> - Show quoted text - >> Stay informed about: Using Named Range in LOOKUP formula? |
|
| Back to top |
|
| |
External
Since: Oct 22, 2008
Posts: 20
|
(Msg. 4) Posted: Thu Oct 23, 2008 4:14 pm
Post subject: Re: Using Named Range in LOOKUP formula?
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Oct 17, 2008
Posts: 19
|
(Msg. 5) Posted: Thu Oct 23, 2008 8:06 pm
Post subject: RE: Using Named Range in LOOKUP formula?
Archived from groups: per prev. post (more info?)
|
|
|
Hi,
You might shorten this approach to something like:
=LOOKUP($I6,INDIRECT(K10),CD)-J6+LOOKUP($I6,INDIRECT(K10),CG)
You can drop the extra parenthesis. I range named your column CD, CG simply
meaning Column D, Column G.
Thanks,
Shane Devenshire
"Ed from AZ" wrote:
> My LOOKUP formula currently reads:
> =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G
> $7)))
>
> I would like to change this so the ranges are columns in a Named
> Range. Something like:
> =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+
> (LOOKUP($I6,Range_Col1,Range_Col7)))
>
> The range name will be selected in a drop-down (data validation) in
> another cell. So "Range_Col1" actually needs to be referenced from
> the value selected in K10.
>
> Can this be done with formulas? Or do I need a macro?
>
> Ed
> >> Stay informed about: Using Named Range in LOOKUP formula? |
|
| Back to top |
|
| |
| Related Topics: | How to make a SumIf range not a range ... but a sum of spe.. - =SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") Here is what I am trying to do: If D3 = AL3 then I want it to return the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 I know it is something simple I am missing .......
how to populate a range with another range of data ? - Hi - imagine that I have 2 worksheets in my workbook. Worksheet 'MASTER' 5 columns x 4 rows Column 1 = KEY and Columns 2 - 5 = numbers (data) Worksheet 'DATA' 4 columns x 12 rows (3 groups of 4 rows) I have been trying to use a combo-box t...
SEARCH and FIND and V.LOOKUP combined - Hi... I wonder... I am using the v.lookup function in a cell. But the text in the matrix and the cell with the lookup value not always are exactly the same. Ex; "Hull" and "Hull City" or "dumb" in "Old dumb fart"...
Using v lookup to return multiple colums with combo box. - I have a worksheet that is using a combo box in B7 to return values from a group on a second worksheet. How do I get the vlookup to return the values in more than one column. I am able to bring back the index number and load it into the cell but canno...
formula error - have any body tried mod(12,2.4) it should return zero but instead it returns a number any clue here |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
