Using Worksheet Name as a variable in formula

N

Nano

Hi guys,

I am taking a value from sheets using formula

=INDEX(Danemark!E2:E49,N10,1)

Where Danemark is the name of worksheet. I have different worksheets
with country names.

Can I use a variable (lets say column E2) instead of the name Danemark
and can choose an worksheet_name which I write in that particular
column E2.

I have tried using INDIRECT but I am not sure how to use it, kindly
help.

Thanking you in anticipation.

Best Regards,
Nano
 
P

Pete_UK

Put "Danemark" (without the quotes) in E2, then you could use this:

=INDEX(INDIRECT("'"&E2&"'!E2:E49"),N10,1)

Note the use of the apostrophes to surround the sheet name in case you
have spaces in those names.

Just change the entry in E2 to select a different sheet.

Hope this helps.

Pete
 
N

Nano

Thanks mate, its working :)

Put "Danemark" (without the quotes) in E2, then you could use this:

=INDEX(INDIRECT("'"&E2&"'!E2:E49"),N10,1)

Note the use of the apostrophes to surround the sheet name in case you
have spaces in those names.

Just change the entry in E2 to select a different sheet.

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

Finding a Value 3
Find team name with highest winning % over multiple years(worksheets) 0
How to make worksheet name as variable? 4
Displaying the name of a worksheet in a cell ? 1
#N/A via Vlookup to produce name from other worksheet 2
Worksheet Name as a Variable 1
Adding a Formula as a data variable in a pivot 5
making a portion of reference to a worksheet variable in a formula 1

Top