CODEWITHSUNDEEP
excelexcel-formula
noobmaster69
noobmaster69

Reputation: 3115

Excel named ranges to make incrementing headers and sub headers

I am creating a spreadsheet application which will require a user to fill in different sections of an input sheet. To separate out these sections into logical means, I am using headers and sub headers as seen below:

Image of input sheet

If I have a lot of headers, there will be a significant amount of manual work in numbering the headers. I have made some attempt at automating the process by creating a Header1 named range which equates to the grey headers in the picture below. So within the A2 and A11 cells, the formula is =Header1. The formula in the Header1 named range is:

IF(COUNTA(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))))=0,0,INDEX(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))),MATCH(ROW(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))),TRUE)))+1

Basically the formula counts all of the values in column A and adds 1 on. In other words it nicely increments every time you call Header1 in your cell, regardless of what worksheet you are in (hence the use of INDIRECT). The only piece of hard coding is the starting cell which is A1 and I have put a 0 in that so it detects to start from 1.

My question is - In a similar manner to how I have achieved incrementing headers using named ranges, how can I do this for sub headers? My picture above shows the effect I am trying to achieve (i.e. 2.1, 2.2) however I want this to be automatic by simply putting a formula of =Header2 in the cell for example.

EDIT - I have gotten as far as this formula:

=SUM(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()-1)))) & "." & MAX(1,COUNTA(INDEX(INDIRECT(CONCATENATE("$B$1",":",ADDRESS(ROW()-1,COLUMN()))),MATCH(SUM(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))))-1,INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()-1))),FALSE)):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))

...which works when placed as a formula in a cell, but doesn't work when used in a named range. Odd!

Upvotes: 3

Views: 1054

Answers (1)

EEM
EEM

Reputation: 6659

Good idea using Defined Names to hold the formula. However your formula is highly volatile.

Suggest to create two defined names at workbook level (scope) as follows:

  1. Named _Hdr (change as required) with the following formula:

    =IF( COLUMN() <> 1, "", 1 
+ MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) )

  2. Named _Sub (change as required) with the following formula:

    =IF( COLUMN() <> 2, "", 0.01 
+ IF( MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) 
> MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ), 
MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ), 
MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ) ) )

Use 0.1 if sub items are less than 10, if higher but less than 100 use 0.01 (adjust as required)

Edit: Add ! to the column ranges in the formulas to ensure that the references will automatically update to the corresponding Sheet where the formula is used.

The formulas above are broken in several lines to ease reading and understanding, enter then as one line when creating the names.

Also suggest to hide the Define Names (i.e. Visible = False) in order to avoid formulas being accidentally altered.

With the above names:

  • There is no need to enter zero at row 1.
  • The formulas work with numbers ignoring any labels.
  • They check first that the formula is entered in the expected column (i.e. _Hdr and _Sub columns 1 and 2 respectively).
  • As the formulas work with numbers they get the MAX number for each column and add 1 or 0.1 to generate the next number.
  • Formulas are no volatile (no use of INDIRECT).
  • They use the INDEX function to generate the required ranges.

Upvotes: 2

Related Questions