What is wrong in formula.
 

Hi,

In my worksheet i have column of IDNo, which is starting from 001. In the
next cell downwards I m using a formula =IF(E60,B5+1,""). But when i search
any
digit like 50 throught ControF. the cursor goes to on 47. Same problem in
other
number search.

why is it.

Regards

What is wrong in formula.
 

Maybe the option the Find is using is "Look in: Formulas", so it has
gone to a cell with the formula containing 50 (eg =IF(E500,B49+1,""),
while the result of the formula is 47.

If this is the case then change to "Look in: Values"

Ken Johnson

What is wrong in formula.
 

Thank you Ken

yours

Rao

What is wrong in formula.
 

Thank you mam?sir?
Is there any way to enter =IF(E110,B10+1,"") this formula the number could
display like 002-2007 or 002-A

Regards
RRS


Maybe the option the Find is using is "Look in: Formulas", so it has
gone to a cell with the formula containing 50 (eg =IF(E500,B49+1,""),
while the result of the formula is 47.

If this is the case then change to "Look in: Values"

Ken Johnson

What is wrong in formula.
 

Rao Ratan Singh wrote:
Thank you mam?sir?
Is there any way to enter =IF(E110,B10+1,"") this formula the number could
display like 002-2007 or 002-A

Regards
RRS

Hi RRS,

Try these formulas...

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-2007","")

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-A","")

Sir:-) Ken Johnson

What is wrong in formula.
 

But how to enter first number by just 1 or 001-2007


"Ken Johnson" wrote:


Rao Ratan Singh wrote:
Thank you mam?sir?
Is there any way to enter =IF(E110,B10+1,"") this formula the number could
display like 002-2007 or 002-A

Regards
RRS

Hi RRS,

Try these formulas...

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-2007","")

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-A","")

Sir:-) Ken Johnson

What is wrong in formula.
 

Dear
I tried this formula but it is returning #value. I want to enter serial
number in this column by using formula in this manner 001-2007, 002-2007,
003-2007 and so on downwards.


"Ken Johnson" wrote:


Rao Ratan Singh wrote:
Thank you mam?sir?
Is there any way to enter =IF(E110,B10+1,"") this formula the number could
display like 002-2007 or 002-A

Regards
RRS

Hi RRS,

Try these formulas...

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-2007","")

=IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-A","")

Sir:-) Ken Johnson

What is wrong in formula.
 

Hi RRS,

I don't know the structure of your sheet, however, I typed '001 (ie
apostrophe zero zero one) into B10 and any positive number into E11.
Then, I entered =IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-2007","")
into A1 (can be any cell).

A1 then showed 001-2007.

If I then filled B10 down column B to produce 002, 003 etc, as well as
enter any old values into E12, E13 etc, filling the formula down in
column A resulted in either 004-2007 for example, when the precedent E
cell was 0 and blank cell when the precedent E cell was <=0.

I thought that was what you were after.

Ken Johnson

What is wrong in formula.
 

Dear mam,
I tried this formula but it is returning #value. I want to enter serial
number in this column A by using formula. First Number I m entering in A6
001-2007 without formula and after using formula the serial number should
return in this manner 001-2007, 002-2007, 003-2007 and so on downwards.



"Ken Johnson" wrote:


Hi RRS,

I don't know the structure of your sheet, however, I typed '001 (ie
apostrophe zero zero one) into B10 and any positive number into E11.
Then, I entered =IF(E110,REPT("0",2-INT(LOG(B10+1)))&B10+1&"-2007","")
into A1 (can be any cell).

A1 then showed 001-2007.

If I then filled B10 down column B to produce 002, 003 etc, as well as
enter any old values into E12, E13 etc, filling the formula down in
column A resulted in either 004-2007 for example, when the precedent E
cell was 0 and blank cell when the precedent E cell was <=0.

I thought that was what you were after.

Ken Johnson

What is wrong in formula.
 

Rao Ratan Singh wrote:
Dear mam,
I tried this formula but it is returning #value. I want to enter serial
number in this column A by using formula. First Number I m entering in A6
001-2007 without formula and after using formula the serial number should
return in this manner 001-2007, 002-2007, 003-2007 and so on downwards.


Hi RRS,

I typed 001-2007 into A6 then this formula into A7...

=REPT("0",2-INT(LOG(LEFT(A6,3)+1))) &(LEFT(A6,3)+1) & "-2007"

In A7 it returned 002-2007.

I then filled the formula down and it returned...

003-2007 in A8
004-2007 in A9
etc, etc, etc.

The formula will only return #VALUE once it is filled down to A1005 and
beyond.
Its final non-error value being 999-2007 in A1004.

Is this what you are wanting?
If so, then I don't understand your original request, which showed a
formula dependent on column E and column B cells.

Ken Johnson (Mr, not Miss or Mrs. Not that it really matters.)

What is wrong in formula.
 

Thank you mam, it is working. Thank you very much for your concern.


"Ken Johnson" wrote:

Rao Ratan Singh wrote:
Dear mam,
I tried this formula but it is returning #value. I want to enter serial
number in this column A by using formula. First Number I m entering in A6
001-2007 without formula and after using formula the serial number should
return in this manner 001-2007, 002-2007, 003-2007 and so on downwards.


Hi RRS,

I typed 001-2007 into A6 then this formula into A7...

=REPT("0",2-INT(LOG(LEFT(A6,3)+1))) &(LEFT(A6,3)+1) & "-2007"

In A7 it returned 002-2007.

I then filled the formula down and it returned...

003-2007 in A8
004-2007 in A9
etc, etc, etc.

The formula will only return #VALUE once it is filled down to A1005 and
beyond.
Its final non-error value being 999-2007 in A1004.

Is this what you are wanting?
If so, then I don't understand your original request, which showed a
formula dependent on column E and column B cells.

Ken Johnson (Mr, not Miss or Mrs. Not that it really matters.)

What is wrong in formula.
 

But my data is more than 1005, then what will I do.


"Ken Johnson" wrote:

Rao Ratan Singh wrote:
Dear mam,
I tried this formula but it is returning #value. I want to enter serial
number in this column A by using formula. First Number I m entering in A6
001-2007 without formula and after using formula the serial number should
return in this manner 001-2007, 002-2007, 003-2007 and so on downwards.


Hi RRS,

I typed 001-2007 into A6 then this formula into A7...

=REPT("0",2-INT(LOG(LEFT(A6,3)+1))) &(LEFT(A6,3)+1) & "-2007"

In A7 it returned 002-2007.

I then filled the formula down and it returned...

003-2007 in A8
004-2007 in A9
etc, etc, etc.

The formula will only return #VALUE once it is filled down to A1005 and
beyond.
Its final non-error value being 999-2007 in A1004.

Is this what you are wanting?
If so, then I don't understand your original request, which showed a
formula dependent on column E and column B cells.

Ken Johnson (Mr, not Miss or Mrs. Not that it really matters.)

What is wrong in formula.
 

Rao Ratan Singh wrote:
But my data is more than 1005, then what will I do.

Hi RRS,

If your data count exceeds 1005 then you should have started with an ID
number pattern that can handle more than 999 data items.

Say your data count is not going to exceed 9999...

then the first ID number you should enter into A6 is 0001-2007, and the
formula starting in A7 is...

=REPT("0",3-INT(LOG(LEFT(A6,4)+1))) &(LEFT(A6,4)+1) & "-2007"

which differs from the previous formula only by a 2 changed to a 3 and
two 3s changed to 4s.

The above does not produce the #VALUE error until row 10005.

Similarly...

=REPT("0",4-INT(LOG(LEFT(A6,5)+1))) &(LEFT(A6,5)+1) & "-2007"

will cater for 99999 ID numbers, and...

=REPT("0",FIND("-",A6)-2-INT(LOG(LEFT(A6,FIND("-",A6)-1)+1))) &
LEFT(A6,FIND("-",A6)-1) +1 &"-2007"

will cater for however many leading digits you use in the A6 entry,
since it uses the find function to determine the correct values to use
in the incrementing formula. Note, however, that the assumption is made
that the only character between the counting digits and the trailling
2007 is the "-" character.


Ken Johnson

What is wrong in formula.
 

It is working. Thank you mam for your continous support.

Regards.

RRS

"Ken Johnson" wrote:

Rao Ratan Singh wrote:
But my data is more than 1005, then what will I do.

Hi RRS,

If your data count exceeds 1005 then you should have started with an ID
number pattern that can handle more than 999 data items.

Say your data count is not going to exceed 9999...

then the first ID number you should enter into A6 is 0001-2007, and the
formula starting in A7 is...

=REPT("0",3-INT(LOG(LEFT(A6,4)+1))) &(LEFT(A6,4)+1) & "-2007"

which differs from the previous formula only by a 2 changed to a 3 and
two 3s changed to 4s.

The above does not produce the #VALUE error until row 10005.

Similarly...

=REPT("0",4-INT(LOG(LEFT(A6,5)+1))) &(LEFT(A6,5)+1) & "-2007"

will cater for 99999 ID numbers, and...

=REPT("0",FIND("-",A6)-2-INT(LOG(LEFT(A6,FIND("-",A6)-1)+1))) &
LEFT(A6,FIND("-",A6)-1) +1 &"-2007"

will cater for however many leading digits you use in the A6 entry,
since it uses the find function to determine the correct values to use
in the incrementing formula. Note, however, that the assumption is made
that the only character between the counting digits and the trailling
2007 is the "-" character.


Ken Johnson

What is wrong in formula.
 

Hi RRS,

You're welcome.
Thanks for the feedback.

Ken Johnson