On 4/23/2016 8:46 AM, GS wrote:
On 4/22/2016 9:41 PM, GS wrote:
Mike,
Typically.., some sort of 'code' or 'flag' is used to show current
status. What these are is entirely user specific such as your suggestion
to use "n/r", for example. I'd avoid using a slash character, though, as
your example "n/a" is an intrinsic error flag.
You don't need to be consistent in the length of the status code IMO as
it's more benefit it be descriptive in context to its usage...
T0 = Taxable at TaxRate0 (0%)
T1 = Taxable at TaxRate1
T2 = Taxable at TaxRate2
NT = Not Taxable
TX = Tax Exempt
...and so on
HTH

Thanks GS,

Are there any tricks for handling these characters instead of numeric
input? Is my best approach to make all of the cells TEXT and then
process them individually looking for either numeric or specific coded
entries (like the ones you listed) when testing for legal inputs? Also
is there a way to have the cells change fill and text color when
certain strings are entered into them? I'm still learning Excel and
wondering how to make this sheet easy to use.
Thanks again,
Mike

Normally, these codes would have their own dedicated column so every
transaction gets a status flag. Depending on how many flags, CF could be
a challenge if allocated to just 1 target cell in 1 column. Your
calculation formulas can be dependant on status codes, obviating need
for user intervention. Hard to advise without seeing a sample of what
you're working with that also shows/notes what expectations you have for
how it should work.

FWIW
I made a rental units manager for the landlord of the commercial space I
was renting some years back. This handled tenants (instead of owners)
and how rents were charged according to the various criteria of this
landlord based on tenant usage of the rented space. This project is
generic in structure (ergo expandable core app) so it can be 'tailored'
to user-specific needs. This landlord also wanted it to include
invoicing and notification printouts for selected/all tenants. I suspect
a housing units management project shouldn't be much different in that
your project handles taxes instead of rents! Perhaps if I can see what
you're trying to do I can better assist...


Right now I think you have given me great advice and I think I can get
this to work, but I have one more question, if it's not clear I can
email you the workbook. If I change the types of cells to text for some
of the data columns, e.g. using the flag "EX" (exempt), I would need to
change the equation that calculates a value for the balance column. Is
it possible to write a function that will add/subtract existing numeric
entries but ignore non-numeric entries? I.e. Right now my balance
calculation looks like this (all cells are numeric):

=(I9-H9)+(L9-K9)+(O9-N9)+(R9-Q9)

If I change L, K, O, N, R, Q to text is there a way to add just I-H to
the balance and ignore text entries that I can paste into the balance
column formula?

Thank you for spending so much time on this, I appreciate it.