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

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

Re: req. for ideas for numeric worksheet

Show all headers | View raw

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.

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