Need cell formula to find sheetname of tab n-tabs before current

[DeBeuz]

Hi guys,

I have a workbook which I'm not allowed to share or publicate.

Inside the there are a lot of tabs, amongst them:
'0' Start of Costcenter tab range
'13134' one of the costcenter tabs
'9' End of Costcenter tab range
'Config' a check/lookup/reference sheet.

Between the '0' and '9' there are one ore more CC-tabs.
They have a name that is the costcenter number (5 digits, starting with a '1').

Is there a cell formula that I can use on the config tab to fetch the sheetname of the first sheet after '0'?
In this example it should return '13134' since it it the name of the first sheet after '0'.

 

[patel45]

Hi guys,
I have a workbook which I'm not allowed to share or publicate.

You can share a sample with no real DATA

 

[DeBeuz]

The uploading procedure looked very complicated.

You can create a new workbook from scrath, containing 8 (empty) tabs.
Name them 'Dummy1', 'Dummy2', '0', '13134', '13135', '9', 'Dummy3' and 'Config'.
I need a formula for e.g. Config!B2 that returns "13134", being the name of the first tab after '0', or the name of the tab 2 positions before '9'.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a cell formula that I can use on the config tab to fetch the sheetname of the first sheet after '0'?

No, you would need vba to do that. Something like this:

Sub FirstSheetAfter0()
    Sheets("Config").Range("B2").Value = _
        Sheets(Sheets("0").Index + 1).Name
End Sub

 

[DeBeuz]

Thanks Peter,

But management has forbidden to put in any vba in those workbooks.
Thanks for the confirmation that it can not be done without vba though.

Kind Regards,

Paul

 

[T. Valko]

Thanks Peter,

But management has forbidden to put in any vba in those workbooks.
Thanks for the confirmation that it can not be done without vba though.

Kind Regards,

Paul

I think this can be done without VBA (technically).

You'd have to use Excel 4 macro functions which are technically not VBA.