software

I use the Indirect function to summarize data from various worksheets;

If I have a worksheet named quot;Luckyquot;, I can easily use the indirect
function. My problem is that I will receive a workbook with a varying
number of worksheets that are named as shipping container numbers.
Each of these are unique and there is no order to them.

I would like to be able to use the indirect function by using the sheet
number instead of the sheet name but have had no success in doing so.

So the question is; quot;How can I use the sheet number in the Indirect
function to retrieve a value?quot;

Thanks

JDB=INDIRECT(quot;'12345'!A1quot;)

or if the number is in cell
A10

=INDIRECT(quot;'quot;amp;A10amp;quot;'!A1quot;)

--
Regards,

Peo Sjoblom

(No private emails please)quot;JDBquot; gt; wrote in message oups.com...
gt;I use the Indirect function to summarize data from various worksheets;
gt;
gt; If I have a worksheet named quot;Luckyquot;, I can easily use the indirect
gt; function. My problem is that I will receive a workbook with a varying
gt; number of worksheets that are named as shipping container numbers.
gt; Each of these are unique and there is no order to them.
gt;
gt; I would like to be able to use the indirect function by using the sheet
gt; number instead of the sheet name but have had no success in doing so.
gt;
gt; So the question is; quot;How can I use the sheet number in the Indirect
gt; function to retrieve a value?quot;
gt;
gt; Thanks
gt;
gt; JDB
gt;Thanks but that does not seem to answer my question.

As I understand it, each worksheet has 2 names; a name and a codename.
If I look at the VBA explorer I can see 2 names for each worksheet
sheet1(Lucky).

Sheet1 is the Codename, and quot;Luckyquot; is the name.

I want to use the Codename in the function Indirect.

Thanks

JDBThat codename can be used for lots of things in code--but there's nothing you
could do with it in a worksheet (well, using stuff built into excel).

You'd have to write your own function to use it and if you did that, you
probably wouldn't need the =indirect() portion.
JDB wrote:
gt;
gt; Thanks but that does not seem to answer my question.
gt;
gt; As I understand it, each worksheet has 2 names; a name and a codename.
gt; If I look at the VBA explorer I can see 2 names for each worksheet
gt; sheet1(Lucky).
gt;
gt; Sheet1 is the Codename, and quot;Luckyquot; is the name.
gt;
gt; I want to use the Codename in the function Indirect.
gt;
gt; Thanks
gt;
gt; JDB

--

Dave Peterson

Not possible using built in functions, Harlan Grove wrote a UDF

tinyurl.com/7cnaf

and if you scroll down a bit more there is a method using Excel 4 macro
however if copied it will crash some versions of Excel

--
Regards,

Peo Sjoblom

(No private emails please)quot;JDBquot; gt; wrote in message oups.com...
gt; Thanks but that does not seem to answer my question.
gt;
gt; As I understand it, each worksheet has 2 names; a name and a codename.
gt; If I look at the VBA explorer I can see 2 names for each worksheet
gt; sheet1(Lucky).
gt;
gt; Sheet1 is the Codename, and quot;Luckyquot; is the name.
gt;
gt; I want to use the Codename in the function Indirect.
gt;
gt; Thanks
gt;
gt; JDB
gt;Thanks to all

JDB