"if" formula not working- seems to be formatting
 

Hi All
I have a formula in Col B =if(G2"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai

"if" formula not working- seems to be formatting
 

=if(G2"99999","StaffType1","StaffType2"),"")

Maybe this...

=IF(B2="","",IF(--G299999,"StaffType1","StaffType2"))

--
Biff
Microsoft Excel MVP


"GaiGauci" wrote in message
...
Hi All
I have a formula in Col B =if(G2"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G
cell
and enter, it seems to correct itself. But I can't do this for all the
cells
or I'll go mad!! I have tried General, text and number formats because I
am
running out of ideas.

Cheers
Gai

"if" formula not working- seems to be formatting
 

--Enter 0 in a cell. Copy the cell
--Keeping the copy Select column G.
--Right clickPasteSpecialAdd. Click OK.

Now try

=if(G299999,"StaffType1","StaffType2")


If this post helps click Yes
---------------
Jacob Skaria


"GaiGauci" wrote:

Hi All
I have a formula in Col B =if(G2"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai

"if" formula not working- seems to be formatting
 

Just paste this formula in B2 cell

=IF(--TRIM(LEN(G2))=5,"STAFF TYPE2",IF(--TRIM(LEN(G2))=7,"STAFF TYPE1",""))

Now copy the B2 cell formula and apply it for the remaining cells.

Change the cell reference G2 to your desired cell if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"GaiGauci" wrote:

Hi All
I have a formula in Col B =if(G2"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai

"if" formula not working- seems to be formatting
 

Thanks, that worked great and I learnt a bit more!!
Cheers
Gai

"Ms-Exl-Learner" wrote:

Just paste this formula in B2 cell

=IF(--TRIM(LEN(G2))=5,"STAFF TYPE2",IF(--TRIM(LEN(G2))=7,"STAFF TYPE1",""))

Now copy the B2 cell formula and apply it for the remaining cells.

Change the cell reference G2 to your desired cell if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"GaiGauci" wrote:

Hi All
I have a formula in Col B =if(G2"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai