Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108788

Re: req. for ideas for numeric worksheet

Show all headers | View raw

> 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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-22 16:43 -0700
  Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 00:41 -0400
    Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 00:18 -0700
      Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 11:46 -0400
        Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 13:49 -0700
          Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 17:04 -0400
            Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:15 -0700
          Re: req. for ideas for numeric worksheet Claus Busch <claus_busch@t-online.de> - 2016-04-23 23:33 +0200
            Re: req. for ideas for numeric worksheet Mike S <mscir@yahoo.com> - 2016-04-23 14:43 -0700
              Re: req. for ideas for numeric worksheet GS <gs@v.invalid> - 2016-04-23 18:32 -0400

csiph-web