Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the Indirect function with a sheet number instead of a sheet name
Thanks to all
JDB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function or number depending on input | Excel Worksheet Functions | |||
Text Function with Different Formatting for Number | Excel Discussion (Misc queries) | |||
Function to return the active sheet name | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) |