|
|
I have the following lookup which is working exactly the way I want:
=VLOOKUP($C$4,Sheet2!$C$2:$M$19,MATCH("1M",Sheet2!$C$1:$M$1,))
The problem is, I need it to change it's lookup based on user input. If the
user types 2 in a certain cell I need this lookup to change to:
=VLOOKUP($C$4,Sheet2!$C$20:$M$37,MATCH("1M",Sheet2!$C$1:$M$1,))
If the user types 3, it needs to change to:
=VLOOKUP($C$4,Sheet2!$C$38:$M$55,MATCH("1M",Sheet2!$C$1:$M$1,))
I can calculate the cell numbers easily enough:
=2+B10*18-18
Which will give me the cell I need to reference, B10 being the cell the user
types 1,2,3, etc into, the result if 2 is entered being 20, if 3 is entered,
it results in 38, etc.
The problem is, I can't figure out how to tell the lookup that the range it
needs to look at has to change. I've tried entering the formula or a
reference to the cell that calculates the change, but that doesn't work.
Any ideas how this might be done?
Thanks
Conf.
|
|
You can use INDIRECT, like this:
=VLOOKUP($C$4,INDIRECT("Sheet2!C"&(B10*18-16)&":M"&(B10*18+1)),MATCH
("1M",Sheet2!$C$1:$M$1,))
Hope this helps.
Pete
On Dec 29, 3:40 pm, "Confused" <nob...[ at ]nowhere.com> wrote:
[Quoted Text] > I have the following lookup which is working exactly the way I want:
>
> =VLOOKUP($C$4,Sheet2!$C$2:$M$19,MATCH("1M",Sheet2!$C$1:$M$1,))
>
> The problem is, I need it to change it's lookup based on user input. If the
> user types 2 in a certain cell I need this lookup to change to:
>
> =VLOOKUP($C$4,Sheet2!$C$20:$M$37,MATCH("1M",Sheet2!$C$1:$M$1,))
>
> If the user types 3, it needs to change to:
>
> =VLOOKUP($C$4,Sheet2!$C$38:$M$55,MATCH("1M",Sheet2!$C$1:$M$1,))
>
> I can calculate the cell numbers easily enough:
>
> =2+B10*18-18
>
> Which will give me the cell I need to reference, B10 being the cell the user
> types 1,2,3, etc into, the result if 2 is entered being 20, if 3 is entered,
> it results in 38, etc.
>
> The problem is, I can't figure out how to tell the lookup that the range it
> needs to look at has to change. I've tried entering the formula or a
> reference to the cell that calculates the change, but that doesn't work.
>
> Any ideas how this might be done?
>
> Thanks
> Conf.
|
|
Aha! Excellent, exactly what I needed, thank you very much.
Conf.
"Pete_UK" <pashurst[ at ]auditel.net> wrote in message
news:46d1b75e-3561-46e3-a6f4-72f312ca9c14[ at ]r15g2000prh.googlegroups.com...
[Quoted Text] > You can use INDIRECT, like this:
>
> =VLOOKUP($C$4,INDIRECT("Sheet2!C"&(B10*18-16)&":M"&(B10*18+1)),MATCH
> ("1M",Sheet2!$C$1:$M$1,))
>
> Hope this helps.
>
> Pete
>
> On Dec 29, 3:40 pm, "Confused" <nob...[ at ]nowhere.com> wrote:
>> I have the following lookup which is working exactly the way I want:
>>
>> =VLOOKUP($C$4,Sheet2!$C$2:$M$19,MATCH("1M",Sheet2!$C$1:$M$1,))
>>
>> The problem is, I need it to change it's lookup based on user input. If
>> the
>> user types 2 in a certain cell I need this lookup to change to:
>>
>> =VLOOKUP($C$4,Sheet2!$C$20:$M$37,MATCH("1M",Sheet2!$C$1:$M$1,))
>>
>> If the user types 3, it needs to change to:
>>
>> =VLOOKUP($C$4,Sheet2!$C$38:$M$55,MATCH("1M",Sheet2!$C$1:$M$1,))
>>
>> I can calculate the cell numbers easily enough:
>>
>> =2+B10*18-18
>>
>> Which will give me the cell I need to reference, B10 being the cell the
>> user
>> types 1,2,3, etc into, the result if 2 is entered being 20, if 3 is
>> entered,
>> it results in 38, etc.
>>
>> The problem is, I can't figure out how to tell the lookup that the range
>> it
>> needs to look at has to change. I've tried entering the formula or a
>> reference to the cell that calculates the change, but that doesn't work.
>>
>> Any ideas how this might be done?
>>
>> Thanks
>> Conf.
>
|
|
You're welcome - hopefully you're not so confused now. <bg>
Pete
On Dec 29, 6:30 pm, "Confused" <nob...[ at ]nowhere.com> wrote:
[Quoted Text] > Aha! Excellent, exactly what I needed, thank you very much.
>
> Conf.
>
|
|
At least not in that area, but there's always something else to be confused
about, lol
Thanks again.
"Pete_UK" <pashurst[ at ]auditel.net> wrote in message
news:fea45b6c-ceda-418c-8e69-e3f391d173b8[ at ]z28g2000prd.googlegroups.com...
[Quoted Text] > You're welcome - hopefully you're not so confused now. <bg>
>
> Pete
>
> On Dec 29, 6:30 pm, "Confused" <nob...[ at ]nowhere.com> wrote:
>> Aha! Excellent, exactly what I needed, thank you very much.
>>
>> Conf.
>>
|
|
|