using the value of a cell within a cell reference.

[nickm687]

Hi everyone,

Dont know if this is a stupid question but i just cant seem to get the right answer.

How can you use the value of a cell with a cell value.

For example, =VLOOKUP(B3,F14:G(F30),2)

F30 has the value 23 in it (which will be changing frequently).

So the actaully range will be when F30 = 23 is F14:G23.

Any help would be appreciated.

Regards
Nick

[Stuart Farr]

I can't think of a way to do this. Why do you need the range to be variable? Perhaps we can come up with a work around. That is of course unless there's someone out there who can come up with a solution?

[solnajeff]

Hi

If you try the following it should work, or at least a variant of the idea.

If you create a named range using the offset function, e.g. Range_offset

Please Login or Register  to view this content.

Then your formula would be

Please Login or Register  to view this content.

Hope that is of some use

Regards

Jeff

[EdMac]

Hi

You can use indirect in your lookup function

VLOOKUP(B3,F14:indirect("g"&F30),2)

Ed