Getting the Name of Tab on the extreme left

[all4excel]

Getting the Name of Tab on the extreme left...

I have some Data which is going to come from a Software i will be Dumping it in the very first sheet on the Left..

There are two ways of following the extraction either by copying the data from the original sheet and then dump it on the sheet where we need to work or just Create a copy and save it in this workbook..

Now for the 1st procedure I can always use my formulas using Indirect function as that Sheet would always be Named as "Dump"..

However, in the 2nd procedure the same sheet would get copied in the Workbook so it would be difficult to hardcode the Name of this sheet...unless explicitly mentioning it.....


Now I know this formula can give me the name of the sheet..

[ MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),(LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))+1)) ]

However, this gives the name of the tab in which the formula is stored whereas I want the name of another tab where I would be either dumping Data or Simply copying the entire sheet, in both scenarios I would not be able to enter the formula..

\Dump\Working\Result

Can someone please help me on this...!

I know its not a big thing to jst get the name, however I would be dealing with a lot of sheets...

[BigBas]

I'm not 100% positive about this, but if I remember correctly it should work.
Take the formula you have:

MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),(LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))+1))

and replace each instance of A1 with the actual file location (ie, Dump!A1). So your formula would look like this:

MID(CELL("filename",Dump!A1),(FIND("]",CELL("filename",Dump!A1))+1),(LEN(CELL("filename",Dump!A1) )-FIND("]",CELL("filename",Dump!A1))+1))

Report back if that works.

Also, if it would help you out, I'm sure there is a VBA method that can give you that sheet name.

[all4excel]

Dear BigBas,

This is a great formula another new thing for me to learn and definitely of great academic importance however this works only if Im dumping the Data by Copying and Dumping the Data from the other file..

However, in reality I may be just copying by the entire sheet from that file and therefore it will always have the Name of the Tab as present in the original file so this formula wont work...

\???Dump???\Working\Result

I have formulas which would work depending on the Sheet on the Extreme left..

If I hardcode the Sheet Name to "Dump" then I would have to always Select and Copy the Data and also the formatting however in the other option the entire sheet would be appear in this Workbook as it is however the Name would always be different and the Names of the Tabs are very weird to base any programming on..

There's a software which actually captures the information for a Swipe In and Swipe Out data and this siftware generates its results in an Excel file with very weird names..

So cant expect it to be Sheet1, 2 ,3 , types..

Hope I have clarifed...

Would appreciate an Excel based solution...

[all4excel]

Bump............................!?