Function to Insert Text Into a Formula

N

navel151

I need a way to get text from a cell put into a function such that the text
fills in the name of the worksheet to look for the data in.

i.e. I want to look for cell B2 in worksheet test, then the next cell down I
want to look for cell B2 in worksheet answer, etc but I don't want to have to
modify the formula to input the worksheet name every time. I want a way for
the worksheet name to be grabbed from an adjacent cell and populate the
formula that already has the cell B2 in it.

Thx.
 
P

Pete_UK

Assuming that you have the sheet names in A2, A3, A4 etc, put this
formula in B2:

=INDIRECT("'"&A2&"'!B2")

then copy down. The apostrophes enable you to have sheet names with
spaces in.

Hope this helps.

Pete
 
N

navel151

TY

Pete_UK said:
Assuming that you have the sheet names in A2, A3, A4 etc, put this
formula in B2:

=INDIRECT("'"&A2&"'!B2")

then copy down. The apostrophes enable you to have sheet names with
spaces in.

Hope this helps.

Pete



.
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

Can I use a worksheet name in a formula? 4
How do I use the text content of a cell as a worksheet reference? 9
convert vlookup formula to link formula 0
Excel 2003 - Copy Down Function to Reference Next Worksheet 3
Copying a formula containing the INDIRECT function 7
function to give me cell reference 5
Using a worksheet function to create an unusual cell reference pat 3
Is there a function that will evaluate a text string in a cell? 2

Top