XL2003 Line Numbering 1

[MeGustaXL]

Hi there,

I have a table which has rows of data split into subheadings by nearly) blank rows, and I want to number only the data rows e.g:

Heading1
1 Data
2 Data
3 Data
Heading2
4 Data
5 Data
.
.
.
There are 280+ rows at present, but that could easily double.

What's the smart way to number these rows, so that I can expand the table or move stuff up/down and have them update automatically?

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Hi,

What is the LOGIC that would identify a heading row from a data row?

I would number ALL rows, but not incriment heading row numbers. Then use Conditional Formatting to "hide" the numbers in the heading rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, this sort of interspersed heading design is not supproted by the Excel Table model.

I VERY STRONGLY advise against such a design.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Hi Skip

Column A = line numbers
Column B = Item Name (or Header)
Columns C thru AB = data/formulas/logical operators, etc

Headers only have text in Column B, all other cells in the row are blank.

Does that help?

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

assuming that your FIRST heading is in B2...
[tt]
A2: =IF(ISBLANK(B2),A1+1,A1)
[/tt]
and copy down.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

I VERY STRONGLY advise against such a design.

LOL, So do I Skip, so do I! But this is a Company layout and thats how it's mandated in our templates.

At the mo' everyone does it mandraulically with Autofilter to hide the blank "C" cells of the Header rows, then type '1', Return, '2', Return... etc. until sleep overtakes you. [frown]

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

You might like this better. It does not put a number in the heading rows at all...
[tt]
A2: =IF(ISBLANK(B2),MAX($A$1:A1)+1,"")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

I DO like that

Except I changed B2 to C2, and then it numbered only the headers! Ha-Ha!!!

So I changed it round like this:

=IF(ISBLANK(C2),"",MAX($A$1:A1)+1)

And it's perfect

More stars for Skip! Thanks M8

Chris

Someday I'll know what I'm donig...damn!