PROBLEM WITH INDIRECT & VLOOKUP FORMULA

G

Guest

Hi, I hope someone can help me

I have the following formula and as I step through it, I can't see any
problem with it, however it keeps returning an error.

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!")&A5

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya
 
P

Pete_UK

Try moving the closed bracket to the end of the formula like so:

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

I assume in your example that you want to get data from 1!A5

Hope this helps.

Pete
 
V

vezerid

Do you want to refer to cell A5 of the chosen sheet? Then:

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5"

The issue here is what does A5 contain. It should contain a cell
address like B1, AF13 etc.

Is there a chance that your sheet names have space? If so:

=INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!")&A5

Notice the extra single quotes.

The solution might be in a combination of the two suggestions.

HTH
Kostis Vezerides
 
G

Guest

Thank you very much, it worked a treat and saved me a lot of time.

Regards
Tanya

Max said:
One guess ..

Try it as:
=INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!A5")

---
Tanya said:
Hi, I hope someone can help me

I have the following formula and as I step through it, I can't see any
problem with it, however it keeps returning an error.

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!")&A5

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya
Click to expand...
Click to expand...
 
G

Guest

Thank you, it works well.

Regards
Tanya

Pete_UK said:
Try moving the closed bracket to the end of the formula like so:

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5")

I assume in your example that you want to get data from 1!A5

Hope this helps.

Pete

Hi, I hope someone can help me

I have the following formula and as I step through it, I can't see any
problem with it, however it keeps returning an error.

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!")&A5

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya
Click to expand...
Click to expand...
 
G

Guest

Thank you for your support, it is greatly appreciated.

Regards
Tanya

vezerid said:
Do you want to refer to cell A5 of the chosen sheet? Then:

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!A5"

The issue here is what does A5 contain. It should contain a cell
address like B1, AF13 etc.

Is there a chance that your sheet names have space? If so:

=INDIRECT("'"&VLOOKUP(B2,D41:F50,3,TRUE)&"'!")&A5

Notice the extra single quotes.

The solution might be in a combination of the two suggestions.

HTH
Kostis Vezerides

Hi, I hope someone can help me

I have the following formula and as I step through it, I can't see any
problem with it, however it keeps returning an error.

Rolls!A4 contains the following formula

=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!")&A5

What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1

Any assistance would be appreciated.
Regards
tanya
Click to expand...
Click to expand...
 
P

Pete_UK

You're welcome, Tanya - thanks for feeding back (to us all).

Pete

Thank you, it works well.

Regards
Tanya



Pete_UK said:
Try moving the closed bracket to the end of the formula like so:

I assume in your example that you want to get data from 1!A5
Click to expand...
Hope this helps.
Pete
Click to expand...

Hi, I hope someone can help me
I have the following formula and as I step through it, I can't see any
problem with it, however it keeps returning an error.
Rolls!A4 contains the following formula
=INDIRECT((VLOOKUP(B2,D41:F50,3,TRUE))&"!")&A5
What I am trying to achieve:
I have 10 worksheets with a list of students names on each. the Vlookup
formula refers to an index [D41:F50] containing the following information:
className Teacher sheet
Eg. 9ISTX Mrs Duffy 1
Any assistance would be appreciated.
Regards
tanya- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INDIRECT & VLOOKUP functions 4
INDIRECT(ADDRESS) inside a VLOOKUP 13
INDIRECT Link of Variables for VLOOKUPs 6
Copying a formula containing the INDIRECT function 7
Use of 'INDIRECT 1
VLookup and Indirect 3
Need help please with VLOOKUP and INDIRECT question 4
Sumproduct with multiple criteria using indirect formula 4

Top