On 4/23/2016 2:04 PM, GS wrote:
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.

I wouldn't 'set' cells to any specific 'data type' unless data type
formatting is required. Leave them as 'General' otherwise. Excel will
ignore non-numeric cells for most formulas so it's okay to sum a range
that also includes text. In fact, there are advantages to doing this if
the numeric data will change by adding/deleting rows/cols within a range
being summed!

Safest way to pass a file here is to post a link to an online storage
space like dropbox or the like. If you want to continue contact via
email then make sure your file has your email address in it. I'm happy
to look at your file and see where I can be of further assistance.

Thanks! I'll give that a try, if I run into problems I'll post a dropbox
link.