Can I use an IF/THEN Statement to format test in Excel 5

[Kallen]

I am not even sure if this is possible, but DESPERATE for a better solution than what we are using now.

I have this spreadhsheet, that tracks accounts by type code.

Example: D101-D177=Account Premium, S110-S453= Account Regualar.

The problem is that the only info that the spreadsheets provide is the Type Codes themselves, and the the account name. This is making monthly analysis a nightmare!

What I would like to do is something like this.

IF Column D is Between D101 AND D177 Then Account Premium
If Column D is Between S110 AND S453 Then Account Regular

I would like this in a seperate column. So the new column would have the proper account names, and it would make it easier to do a pivot table.

I would think this can be done in Excel, I am sure I am not the only one who gets thousands of records like this on a monthly basis.

Does anyone know if this is possible?

Thanks

 

[DaleWatson123321]

Kallen,

I created and tested the following formula, and it works.

You should copy it directly from here, and paste it into your worksheet.

=IF(AND(LEFT(D2)=&quot;D&quot;,VALUE(MID(D2,2,999))>=101,VALUE(MID(D2,2,999))<=177),&quot;Account Premium&quot;,IF(AND(LEFT(D2)=&quot;S&quot;,VALUE(MID(D2,2,999))>=110,VALUE(MID(D2,2,999))<=453),&quot;Account Regular&quot;,&quot;&quot)

Please advise as to how it works.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

That's ugly, Dale! LOL LOL

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[DaleWatson123321]

THANKS, Dreamboat,

...of course I'll take that as a complement.


Regards, ...Dale

 

[GeekGirlau]

Kallen, if you're allergic to long formulas as I am (even though it works beautifully Dale!) you could create a function for this. They're a little easier to follow, especially if someone else ends up inheriting your spreadsheet in the future.

Function AccountType(strAccCode As String) As String
Dim intCode As Integer


intCode = CInt(Right(strAccCode, Len(strAccCode) - 1))

Select Case UCase(Left(strAccCode, 1))
Case &quot;D&quot;
If intCode >= 101 And intCode <= 177 Then
AccountType = &quot;Account Premium&quot;
End If

Case &quot;S&quot;
If intCode >= 110 And intCode <= 453 Then
AccountType = &quot;Account Regular&quot;
End If
End Select
End Function

To use the function in your worksheet, the formula is

=AccountType(D1)

 

[Kallen]

I have tried both, and wouldn't you know it, I am having probelms w/ both. When using the formula, I get a message letting me know that there is an error in my formula, and gives me the usual info. on how to format a formula.

When using code, I get the &quot;Case without Select Case&quot; error, when it brings me back to VBA, it is highlighting Case S.

I have not given up though, I know I am doing something wrong, and hope to work on this later on today to see what it is.

Thanks, and I will let both of you know what happens.

 

[GeekGirlau]

Make sure you have double quotes around the case statement: should be

Case &quot;S&quot;
not
Case S

 

[DaleWatson123321]

Kallen,

When you copy and paste a formula from Tek-Tips, it often will end up being pasted as a &quot;merged set of cells&quot;. Therefore, you need to &quot;un-merge&quot; these cells.

To &quot;un-merge&quot; the cells, use &quot;Format Cells - Alignment, and then de-activate &quot;Merge&quot;. Once de-activated, move the formula to any column on ROW 2 (because the formula refers to row 2).

To get rid of the blue color background, simply use the &quot;Paint Can&quot; icon and choose &quot;No Fill&quot;.

When you enter a &quot;valid&quot; value into cell D2 - i.e. D101 to D177, or S110 to S453 - the formula will return the desired result.

You then just need to copy the formula down the column, for the number of rows you require the formula.

I hope this clarifies the situation, and that you can now make use of the formula. Knowing how to consturct formulas can be useful knowledge, and I hope this will serve as an example for you.

GeekGirlau's example of a custom function is ALSO a GOOD alternative, and certainly worth a STAR. Thanks GeekGirlau! Your example of a customized function is a good one which all Tek-Tips users can benefit from.

While I appreciate that custom functions can be preferable in some situations, I ALSO want to make the case for the use of FORMULAS in some situations. One of the advantages of formulas, is that they can be made to be “more interactive” for the user – in terms of setting up separate “control” cells to which the formula refers, and allows the user the added flexibility of being able to easily “adjust” the formula if required. Obviously the same sort of flexibility can be built into custom functions, so I accept that it becomes a matter of “preference”, as well as a matter of the “context” of the task at hand.

A final point on learning and using complex formulas… It can result in “opening the door” to solving VERY complex tasks involving SEVERAL formulas which provide “components” of a MUCH larger task – one which would be an EXTREME challenge using custom functions. I give as an example, thread68-150301 dated October 17, 200, entitled “Find Numeric Text in Cell”. “CurtR” was able to utilize my “set of several formulas” to extract addresses out of a HUGE list wherein the fields were NOT consistent in length.

Anyway, Kallen, it would be to your advantage to learn BOTH options.

And thanks again to GeekGirlau.

Regards, …Dale Watson dwatson@bsi.gov.mb.ca

 

[Kallen]

GeekGirlau,

Thanks!! The Code worked like a charm. What I forgot to do was put &quot;End If&quot; after Case &quot;D&quot;.

I have a tendency to use formulas in Excel (though not as sophisticated as Dale Watson's), and save my VBA for Access. I now see how easy it is to use VBA in Excel. I always wondered how I would execute it.

Not only did the two of you help me in my dilema, but you also gave me &quot;food for thought&quot; on other ways I can make MIS reporting in Excel easier.

THANKS AGAIN!!!!!!!!!!