Excel Formula Help needed on Sheet name retrival

[rahul_nair01]

I have an existing formula in a cell as : =[Sample.xls]OC1!$F$1
You will understand that it is taking the value of the Cell F1 of Sheet OC1 of the excel workbook Sample.xls
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I do not want to hardcode the sheet name, how to write the sheet number there… The sheet name can change but the number of that sheet wont.
something like =[Sample.xls]'Sheet(2)'!$F$1 ' I know it wont work, but somthing like it
<o> </o>
Suggestions invited on how to do it.
<o> </o>
Regards,<o></o>
Rahul Nair<o></o>

 

[AnalyticsGuy7]

I'm not sure if it will help you, but you could add a custom function that returns the sheet name using an index number.

Function SheetName(index As Variant)
SheetName = Worksheets(index).Name
End Function

Then your formula could be as follows:

=INDIRECT("[Sample.xls]"&SheetName(1)&"!F1")

 

[rahul_nair01]

Thanks for your reply.
The problem is that the sheet names are dynamic and cannot be determined.
Another way in which i thought i could solve this problem was by Getting the sheet names requred in the Workbook Open function. But you can imagine the face of the user if 50 more workbooks are opening and closing when he is opening just one workbook...

 

[AnalyticsGuy7]

The custom function is designed for the fact that the worksheet names are dynamic. You said, however, that the NUMBER of the sheet wont. That is what the function does. It PULLS the NAME of the sheet based on the Number.

I also should have mentioned that the custom function goes in the VBA code if you haven't used it already.

Give it a try and see if it works. Also, note that when using the Indirect function, you can make your workbook names reference from another cell.

 

[rahul_nair01]

Hi,
I had just tried your solution. The problem i was facing is that the Function SheetName does return the Sheet name of the current workbook and not the sheet name of the workbook Sampls.xls

 

[AnalyticsGuy7]

Sorry for getting back to you late.

I have updated the Function...

Function SheetName(index, Wkb)
SheetName = Workbooks(Wkb).Worksheets(index).Name
End Function


Now the Formula could read...

=INDIRECT("[Sample.xls]"&SheetName(1,"Sample.xls")&"!F1")

 

[rahul_nair01]

Thanks buddy... its working. Only constraint is that the second workbook need to be open. Using ADOX i had devised function to get the details when the other workbook is closed,.