auto number and count cells not values within the cells

[Judi J]

Hi,

I hope someone can help me. I have a linked workbook. One
workbook has several sheets. Each sheet contains a
numbered list of buildings of a certain category - I have
5 worksheet of this.

For example: Sheet 1 list Class A, B and C Buildings.
On sheet 1, under Class A Buildings - cell A1 would list
the name of a Class A building, B1 would list it as 1.
Cell A2 would list the second Class A building name and B2
would list it as 2 and so on.

In Microsoft Word, you can just click on "bullets and
numbering" and it numbers it for you each time you hit the
enter key. Can you do this in Excel somehow?

I would like the numbering to stop at a certain cell on
Sheet 1 and then resume the previous numbering on Sheet 2
for the Class A Buildings listed there.

I thought of a "Count" function but I don't want to count
the values within the cell, I just want to count the
number of cells regardless of the value inside them. For
example: If I have 5 cells with numbers in them from 1 to
5, I would like the total to be 5, NOT the sum from inside
the cells. Hope this makes sense.

That being said, I would like the total to be linked to
another workbook so that it automatically reflects all
changes. This I can handle once I have a cell with the
proper formula in it for counting the cells in question.

Any help would be greatly appreciated - thanks.

 

[hgrove]

Judi J wrote...
...

For example: Sheet 1 list Class A, B and C Buildings. On sheet 1,
under Class A Buildings - cell A1 would list the name of a Class A
building, B1 would list it as 1. Cell A2 would list the second Class
A building name and B2 would list it as 2 and so on. ...
I would like the numbering to stop at a certain cell on Sheet 1
and then resume the previous numbering on Sheet 2 for the
Class A Buildings listed there.

...

As described, you could enter 1 in Sheet1!B1, then enter the formula
=B1+1 in Sheet1!B2. Enter as many buildings as you need in column A i
Sheet1, then select cell B2 and double click on the Fill Handle (th
square in the bottom-right corner of the border around the selecte
range. This should fill the formula in cell B2 down into cells below i
in column B corresponding to cells in column A containing buildin
names.

You could continue the numbering in sequence on Sheet2 by entering th
following formula in Sheet1!B1.

=LOOKUP(1E300,Sheet1!B:B)

Enter the same formula in Sheet2!B2 as in Sheet1!B2,

=B1+1

Enter more building names in Sheet2 column A, then fill column B i
Sheet2 the same way you filled column B in Sheet1

 

[Judi J]

Thanks so much for your help! I got some of this to work
but I don't understand where you got the 1E300 part in
the "lookup" formula.

This is what I did:

SHEET 1 - A1 is the Building Name, A2-A21 are the Building
Numbers.
B1 is the Building Numbers. In B2, I typed the number 1.
In cell B3, I typed =B2+1 then dragged down the column to
get sequential numbering and stopped at cell B21 (so
numbering ends on 20).

SHEET 2 - A- is Building Name, A2-A7 are building names
B1 is the Building Numbers
In cell B2, I typed =Sheet1!B21+1
In cell B3, I typed =B2+1 and then dragged down the column
to get sequential numbering ending at cell B7 (so
numbering ends on 26)

SHEET 3 - A1, A2, A3 merged for title input
A2 is Class A, B2 is Class B, C2 is Class C
In cell A3, I typed =Sheet2!B7

This accomplished the tasks of sequential numbering across
2 sheets and reflecting the correct number in cell A3 on
Sheet 3 to match cellB7 on Sheet 2. However, if I insert a
row on sheets 1 or 2, it stops numbering sequentially
which of course, messes up the total on sheet 3 in cell
A3. I really need to be able to insert rows periodically.

Can you please explain to me how to fix this again? I
realize the scenario I just explained is different but I
thought it might make more sense.

Thanks again for responding!

 

[Harlan Grove]

Thanks so much for your help! I got some of this to work
but I don't understand where you got the 1E300 part in
the "lookup" formula. ....
This accomplished the tasks of sequential numbering across
2 sheets and reflecting the correct number in cell A3 on
Sheet 3 to match cellB7 on Sheet 2. However, if I insert a
row on sheets 1 or 2, it stops numbering sequentially
which of course, messes up the total on sheet 3 in cell
A3. I really need to be able to insert rows periodically.

It's because I was anticipating this problem that I suggested the LOOKUP
formula to carry over sequential numbering from previous worksheets. If
given a number as its first argument that's larger than any number in it's
array second argument (1E300 = 10^300, which is safely larger than the
number of buildings you'd be working with), LOOKUP returns the *last* number
in its array second argument. That means the LOOKUP formula I gave you would
return one more than the largest number in column B on the preceding
workbook. Unless you insert or delete columns, you can insert or delete as
many rows as you want, and the LOOKUP formula will still work.

So don't second guess the total solutions other people give you. If you need
an explanation why certain things work the way they do, just ask.

 

[Judi J]

Hi Harlan,

Thanks for replying again. I'm not trying to "second
guess" you. I'm just trying to make this work in the
meantime while I'm waiting for an answer that makes sense
to me - I'm pretty much a novice with Excel.

While I do understand the "lookup" logic of what you are
telling me and it seems the result of this logic is
exactly what I am trying to produce, I don't understand
what "1E300" refers to. I understand what 10^300 means.
Is "1E300" a formula for that? The "E" part isn't
referring to a column is it (eg. cell E1)?

Please don't be mad at me.

 

[Harlan Grove]

1E300 is standard computer and engineering shorthand for 10^300.
Specifically, xEy = x * 10^y.