| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Diff. change headline in "Formatting" or "Outlining" toolbar? | Excel Discussion (Misc queries) | |||
| Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Working out age from "Day" "Month" "Year" | Excel Worksheet Functions | |||
| Formula not working -- =SUMIF($F$6:$F$91,"=90",G6:I91) | Excel Discussion (Misc queries) | |||
Linear Mode
