I'm not sure if this should go here or not, but I thought I would try here first.



I have a workbook that performs a query to an Oracle database via an ODBC connection. I have a typical "select, from, where" statement to pull down my information. I have a few formulas in various columns to do some formatting and other things.

When the data is refreshed the query might not return any records. The problem is when the query is refreshed the formulas are cleared out if no data is returned.

The end state that I am trying to reach is the data coming from the database is formatted correctly. There are three instances where this is causing a problem.

1) "TABLE"."Field" in my command text returns one of two phrases. Either "Red Car" or "Blue Truck". My current formula in the excel table is =if(a2="Red Car", "RC", "BT")
2) "TABLE"."Field" returns a string of numbers formatted "32-40". I am currently using the Left function as I only need the first two numbers.
3) "TABLE"."Field" returns a dollar figure. The first formula I use is to change entries that return a ""$0.00". in column d=if(B2 = 0, "--", B2). I then use another formula to restrict these records based on criteria. =if(C2="Yes", "D2", "--")

What I would like to do is to be able to use the Command Text for the external connection to perform the formatting if possible (just makes things cleaner) but I will be happy for my formulas to hold upon refresh.

Thanks in advance.