Excel - Increment a number based on row input 1

[Bill4tektips]

I have a spreadsheet with various columns and I want the number in column A to incement if data is input to other columns. What I have tried so far is:
=IF(B5:K5="",A4+1,0)where columns B to K have data and column a has the number. Any help would be appreciated.

 

[GlennUK]

Have a look at the COUNTA function and see if it helps. I'm not sure that I understand what you are trying to achieve.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

Glenn, what I am trying to achieve is to get cell A1 which contains a number like 00000001 to increment in A2 when an entry is made in B2:K2

 

[SkipVought]

Hi,
[tt]
=IF(B5:K5="",A4+1,0)
=IF(COUNTA(B5:K5)=10,A4+1,0)
[/tt]
Also I would recommend using the TABLE feature, so that you will not need to pre enter formulas in empty rows, which is a REALLY POOR IDEA.

Skip,

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

 

[GlennUK]

Glenn, what I am trying to achieve is to get cell A1 which contains a number like 00000001 to increment in A2 when an entry is made in B2:K2

... and what is to happen when no entries are made in B2:K2? Is the result to be zero ( as you seem to have hinted ), or is it to simply duplicate the number above?



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

There should be no case where an entry is not made in B2:K2

 

[GlennUK]

There should be no case where an entry is not made in B2:K2

...

Glenn, what I am trying to achieve is to get cell A1 which contains a number like 00000001 to increment in A2 when an entry is made in B2:K2

Do the cells in B2:K2 change from having no entry to having an entry? It seems like your statements are at odds!

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

Yes! but once an entry is made it will never be removed, changed perhaps but never removed.

 

[GlennUK]

Then the point is that the state of the cells initially is that they have no entry, surely?

And the question I asked is: what should a formula in cell A2 do in that case?

This is a minor part of the picture ... I haven't even touched on what to do when an entry is made - Skip already made a suggestion for that part ... did that work for you?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

No your previous suggestion did not work.
Just to clarify, when you open the spreadsheet the rows will be blank. On an input to a cell in column B to K it will allocate it the next number in the sequence in column A.
Hope this makes things clearer.

 

[GlennUK]

You still haven't said what a formula is to do when then cells are blank ... I'll assume that you want a zero.

=IF(COUNTA(B2:K2)>0,MAX($A$1:A1)+1,0)

entered in A2 and copied down.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bill4tektips]

Glenn, It works, thanks for that.
Unfortunately it doesn't work on alpha numeric numbers but I overcame that by puting the alpha characters in one column and the numeric in another column and then concatenate the two together which gives me the result I was looking for. Thanks again

 

[GlennUK]

Great! Glad that you've been successful.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[randy700]

Unfortunately it doesn't work on alpha numeric numbers

What alpha numeric numnbers?

Randy

 

[Bill4tektips]

I found out after my initial post that they wanted alpha numeric numbering sequence i.e. DG00001.
It is OK with the method I have used.