corsasport.co.uk
  Not logged in [Login - Register]  
You Are Not Registered Or Not Logged In  

Corsa Sport » Message Board » Off Day » Geek Day » Excel Formula Help.. » Post Reply

Post Reply
Who Can Post? All users can post new topics and all users can reply.
Icon:
Formatting Mode:
Normal
Advanced
Help
Insert Bold text Insert Italicized text Insert Underlined text Insert Centered text Insert a Hyperlink Insert Email Hyperlink Insert an Image Insert Code Formatted text Insert Quoted text
Message:
HTML is Off
Smilies are On
BB Code is On
[img] Code is On
Post Options: Disable smileys?
Turn BBCode off?
Receive email notification of new replies?

Bart

 posted on 7th Dec 06 at 07:59

i will try to explain again. but ive kinda gotten the formula now and it works REALLY well in excel... perfectly, does exactly what i want it to do. but for some reason sharepoint wont accept it.. syntax error.

Id like to be able to use Sharepoint to generate job numbers based on the
first letter of the customer name and then adding a predefined figure. (lets
say 1000).

At the moment, in excel, in column A i have customer and in column b i have
the following formula:

quote:

=IF(ISBLANK($A2),"",LEFT($A2,1)&COUNTIF($A$1:$A2,LEFT($A2,1) & "*")+999)


So if the customer begins with 'A' i get A1000
If it begins with B i get B1000
If i add another A i get A1001 and another B i get B1001 and so on.
So at the moment theres no chance of my duplicating the job numbers.
It works really well in excel, but it wont let me use this formula in
Sharepoint.

Im quite new to sharepoint.

Previously i had

quote:

=LEFT(Customer,1)&"1000"


Which worked in Sharepoint, it added the first letter to 1000, but was
obviously duplicating the job numbers.

I have tried importing the excel document into WSS but it doesnt appear to
copy the formula in :(

Adam


MikeLamb

 posted on 6th Dec 06 at 20:32

Increase what digit by1?


Ian

 posted on 6th Dec 06 at 19:14

You want to check the entire column?

ie. H1,H2,H3....Hn ?


Bart

 posted on 6th Dec 06 at 11:39

oh? i thought it was

u=a(h0m0) = steve

Thanks for your contribution :)


Steve

 posted on 6th Dec 06 at 11:37

I believe you need

=LEFT(T1,T)&SMB(STFU(B4LLS)+1)


AndyKent

 posted on 6th Dec 06 at 11:05

=LEFT(A1,1)&VALUE(RIGHT(B1,4)+1)

is that what you mean?


Bart

 posted on 6th Dec 06 at 10:37

I have this Formula so far:

=LEFT(b1,1)&"1000"

(the above formula is in Cell H)

At the moment, if B1 starts with the letter W i get w1000 in H1
Is there a way to check Column H to see if w1000 exists and if so, increase the digit by 1?