Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JDB
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheet name

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

If I have a worksheet named "Lucky", 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; "How can I use the sheet number in the Indirect
function to retrieve a value?"

Thanks

JDB

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheet name

=INDIRECT("'12345'!A1")

or if the number is in cell
A10

=INDIRECT("'"&A10&"'!A1")

--
Regards,

Peo Sjoblom

(No private emails please)


"JDB" wrote in message
oups.com...
I use the Indirect function to summarize data from various worksheets;

If I have a worksheet named "Lucky", 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; "How can I use the sheet number in the Indirect
function to retrieve a value?"

Thanks

JDB


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JDB
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheet name

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 "Lucky" is the name.

I want to use the Codename in the function Indirect.

Thanks

JDB

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheetname

That 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:

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 "Lucky" is the name.

I want to use the Codename in the function Indirect.

Thanks

JDB


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheet name

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

http://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)


"JDB" wrote in message
oups.com...
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 "Lucky" is the name.

I want to use the Codename in the function Indirect.

Thanks

JDB




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JDB
 
Posts: n/a
Default Using the Indirect function with a sheet number instead of a sheet name

Thanks to all

JDB

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function or number depending on input Jan Jansens Excel Worksheet Functions 7 September 7th 05 12:40 PM
Text Function with Different Formatting for Number MKenworthy Excel Discussion (Misc queries) 1 September 1st 05 11:34 PM
Function to return the active sheet name Will Excel Worksheet Functions 2 August 23rd 05 04:33 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 04:15 PM


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"