# Use text from cell as sheet name in formula

1. ## Use text from cell as sheet name in formula

I am trying to re-learn excel after a long illness and I have come against a problem.

I have tried the answers I thought might work, but I must be doing something wrong.
I have a formula that works

=INDEX(Sharp!\$B\$2:\$N\$212,MATCH(\$D\$3,Sharp!\$A\$2:\$A\$212,0),1)

I need to replace the 2 sheet names (Sharp) with the name that appears in cell B3.
This cell (B3) uses a dropdown box to choose the manufactures name and I have each sheet labelled with the manufactures name.
So, when I change the name in cell B3 using the dropdown I want the formula to use that sheet name.
I am using Excel 365 and Win10.
Sorry, if I haven't explained this very well but I'm still re-learning excel. Thanks in advance.

2. ## Re: Use text from cell as sheet name in formula

One way:

=INDEX(INDIRECT("'"&\$B\$3&"'!B2:B212"),MATCH(\$D\$3,INDIRECT("'"&\$B\$3&"'!A2:A212"),0))

3. ## Re: Use text from cell as sheet name in formula

Try

=INDEX(INDIRECT("'"&B3 &"'!\$B\$2:\$N\$212"),MATCH(\$D\$3,INDIRECT("'" & B3 &"'!\$A\$2:\$A\$212"),0),1)

4. ## Re: Use text from cell as sheet name in formula

JT... You seem to be consistently 3 minutes too slow today!!!

5. ## Re: Use text from cell as sheet name in formula

Originally Posted by Glenn Kennedy
One way:

=INDEX(INDIRECT("'"&\$B\$3&"'!B2:B212"),MATCH(\$D\$3,INDIRECT("'"&\$B\$3&"'!A2:A212"),0))
Amazingly fast. Thank you very much Glenn, you saved me from going mad, It works..

6. ## Re: Use text from cell as sheet name in formula

Originally Posted by JohnTopley
Try

=INDEX(INDIRECT("'"&B3 &"'!\$B\$2:\$N\$212"),MATCH(\$D\$3,INDIRECT("'" & B3 &"'!\$A\$2:\$A\$212"),0),1)
Thank you very much, this too works

7. ## Re: Use text from cell as sheet name in formula

@Glenn
Got give you "old" guys a chance!!! Or more correctly, I AM just slooooow!!

8. ## Re: Use text from cell as sheet name in formula

JT.Indeed. Feeling a bit ancient today!!

9. ## Re: Use text from cell as sheet name in formula

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

There are currently 1 users browsing this thread. (0 members and 1 guests)