Vlookup-function - possible to refer to a cell?

[bankman]

Who can help me with the following problem?

I have build a report in excel. In one worksheet I use th
Vlookup-function to get data from another worksheet. The function look
like this:

=VLookup(A3;'Name of worksheet'!$B$5:$P$17;7;FALSE)

The piece "$B$5:$P$17" refers to the cells where to look for the data
need. But this little piece of code varies. Hope I write this correctl
(I'am from the Netherlands) ;-).
I mean I've got 60 different codes like this which I use in +/- 18
rows and 13 colums (that's 2340 cells!!).

In another worksheet (Codes) I've made some kind of tool whic
generates these codes for me. These codes can change very often and
don't want to 'search and replace' all these codes in all 2340 cell
every time they change.

Is there some way to replace the piece "$B$5:$P$17" in th
VLookup-function with a refer to a cell in the worksheet "Codes" ? ?

When I try to do it like this it does not work:

=VLookup(A3;'Name of worksheet'!(Codes!I6);7;False).

Does anyone have a suggestion for me???

I hope I have explained my problem clearly?

Thanks in advance for helping me.

Patric

 

[gilbert]

Hi Patrick,

If it is single cell, why you need to use Vlookup to complicate th
matter? You can always use IF function to achieve what u want....

eg. if(A3>=0,Name of worksheet!I6,"")

I think that would work.....maybe someone could put in some thought
also...

Rgds,
Gilber

 

[Frank Kabel]

Hi
try
=VLookup(A3;INDIRECT("'Name of worksheet'!" & 'Codes'!
I6);7;False)

 

[bankman]

Gilbert, ThnQ 4 your reply, but the problem / difficulty is that's no
onlty 1 cell. But a whole bunch of them. But thanks anyway.

Frank, your advise seems to bring me a little closer to my goal. Now a
least I don't get a error-message form excel.

The cell now shows: #N/B

I guesse the problem now is, that I use a dutch-version of Excel an
that "Indirect" is called different in Dutch. Now I have to see what i
excel the command "Indirect" is in Dutch.

Can you tell me what "INDIRECT" does in Excel? So I can try to use th
help-function in excel to c what the dutch word is?

Thanks a lot for your help thusfar.

Greetz,
Patric

 

[Frank Kabel]

Hi
have a look at
http://www.contextures.com/functions.html
Download Norman Harker's function list for translations.
Dutch should be included.

 

[bankman]

Frank, I've found that INDIRECT is also INDIRECT in the Dutch version o
excel.
So, it still doesn't work. Do you maybe have another suggestion?
think we can't be far from the solution.

ThnQ in advance again.

Patric

 

[bankman]

Frank,

Sorry, I did something wrong myself. I refered tot he wrong cell I8.
It had to be I81. Now it works. Thanks a lot !!!!!!!

Best regards and take care,
Patric