formula range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ADK - 15 Jun 2007 12:43 GMT

is there any way to make A2 in the formula a function of a cell value?

=VLOOKUP($B$1,A2:D1969,3,FALSE)

Example: if F1 = the value of: "A50"

So the formula in A1 would be:

=VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message

Roger Govier - 15 Jun 2007 12:53 GMT

Hi

Try
=VLOOKUP($B$1,INDIRECT("'"&F1&"'!:D1969"),3,FALSE)

Note the quotes
" ' "&F1& " ' !:D1969"

Signature

Regards

Roger Govier

> is there any way to make A2 in the formula a function of a cell value?
>
[quoted text clipped - 5 lines]
>
> =VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message

bj - 15 Jun 2007 14:49 GMT

I don/t think the ' and ! should be there.
This is a cell reference, not a sheet reference

Is there a benefit, I am not aware of?

> Hi
>
[quoted text clipped - 13 lines]
> >
> > =VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message

Roger Govier - 15 Jun 2007 16:27 GMT

You are quite correct. My mistake.
So used to dealing with sheets, I type it automatically<g>

Signature

Regards

Roger Govier

>I don/t think the ' and ! should be there.
> This is a cell reference, not a sheet reference
[quoted text clipped - 19 lines]
>> >
>> > =VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message

ShaneDevenshire - 15 Jun 2007 15:10 GMT

Hi,

Here is a slightly simplier formula:

=VLOOKUP($B$1,INDIRECT(F1&":D1969"),3,FALSE)

Signature

Cheers,
Shane Devenshire

> is there any way to make A2 in the formula a function of a cell value?
>
[quoted text clipped - 5 lines]
>
> =VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message

ADK - 15 Jun 2007 18:46 GMT

Thanks...this one worked great!

> Hi,
>
[quoted text clipped - 11 lines]
>>
>> =VLOOKUP($B$1,A50:D1969,3,FALSE)

Reply to this Message