Formula to create a counter base on "@"

Question

Hi,

I am using an old excel that is organize in 3 levels of headers: First: 1 Second 1.1 and Third: 1.1.1

The Excel uses a "formula" not sure if is really a formula, to count the number of the next header. For example, in column A is the First header, if you use the formula =header1 in Cell A7 (See picture below) returns the number 1, then in Cell A17 if you include the same formula =@Header1 it returns number 2.

I don´t have access to the person who develops this excel.

There is no name define as "Header1" nor nothing related to header.

This header counting only works on one specific sheet, if I open a new sheet the formula doesn´t work.

Same happens with Second Header, if you use =@Header2 returns 1.1 if is the first time you use it in column B. Then if you use it again return 1.2.

The Header 1 , Header 2 and Header 3 are related, in a way that if in Header 1 returns the number 2, then in header 2, if you use it, returns 2.1. (See secound picture below)

Can you please help me to understand how can I do this?

Answer 1

Hi Roberto Ramirez5,
Thanks for your post in Microsoft Community.

I noticed that you mentioned this is an old Excel document and you cannot contact its author; I assume it originates from an Excel workbook found on the internet. 

If you confirm that there is no "Header" defined in the file's Name Manager, this suggests that the formula or function could only have been generated by VBA (Visual Basic for Applications). 

Firstly, the @ character is used to handle special intersection operators in Excel, typically dealing with implicit intersections in dynamic arrays. Specifically, you can refer to: Implicit intersection operator: @ - Microsoft Support. 

If the @ character is used safely without causing any unusual cell overflow issues, this indicates that VBA was used to process this special field in response to specific content. 

Based on my limited experience writing VBA scripts, I suspect that a custom iterator was defined within a VBA-defined function, which splits decimals based on numbers following the "Header" character. Each call to this custom function increments this iterator, ensuring that each call returns the corresponding header level. 

However, this is just my initial thought, and for more detailed VBA-related content, I recommend seeking support from more specialized experts. You might want to post your question on Stack Overflow.

Ask vba programming questions to Stack Overflow by using the vba tag along with any other relevant tags.

Please note that Stack Overflow includes guidelines, such as requesting a descriptive title, a complete and concise problem description, and sufficient details to reproduce your problem. Feature requests or questions that are too broad are considered off-topic.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

I can't help you, but I'll leave this question open in case one of our great volunteers has ideas for you. 

Best Regards, 
Thomas C - MSFT | Microsoft Community Support Specialist