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.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus