sequential numbering in excel 2003

i would like to get a formula/vba to place sequential numbers in column b and also to find the first blank in a range
numbering.xls
SvgmassiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Should the sequential numbering only apply to rows with values in column [E], like your example?  Or, do you simply want the numbers 1 to 14 in cells [B19:B32]?

(Mind you, I am unsure why cell [B22] has a value of 3, yet there is no value 4 as cell [B25] has 5)

For all rows in this range to be filled...

Place this formula in cell [B19]:
=ROW()-18
Copy this cell down column [ B ] to [B32].

For rows with values just in column [E]...

Place this formula in cell [B19]:
=IF(ISBLANK(E19),"",COUNT(E$19:E19))
Copy this cell down column [ B ] to [B32].

Please see the attached workbook for examples of both of these methods.


Finally, how would you like "the first blank" (in range [E19:E32]) to be identified?  By row, by cell reference, or by some other method?

Thanks for your clarification.

BFN,

fp.
Q-28228076.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SvgmassiveAuthor Commented:
cell reference
0
[ fanpages ]IT Services ConsultantCommented:
Entered as an array formula (use [CTRL]+[SHIFT]+[ENTER] instead of just [ENTER]):

="E"&MIN(IF(LEN(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1))=0,ROW(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1))))

See cell [I23] in the (updated) attached workbook.

Note: This formula returns "E0" when cells in the range may appear blank but are, in fact, empty.

Assuming that the contents of [E19:E32] will always be numeric values, then this formula will report the correct address:

="E"&IF(MIN(IF(LEN(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1))=0,ROW(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1))))=0,ROW(E19)+MATCH(9.99999999999999E+307,E19:E32),MIN(IF(LEN(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1))=0,ROW(OFFSET(E19,0,0,MATCH(9.99999999999999E+307,E19:E32,1)-1,1)))))

See cell [I25] within the attachment.
Q-28228076b.xls
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.