Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Spokane, WA, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using IF to put a value in a remote cell (XP)

    Here is what I want to do: When a deposit is made I want to have it automatically distributed according to preset percentages. However, if it is an expense, then the user will distribute the according to the nature of the expense.
    <UL><LI> if C* (*=any cell in Column C) is blank then I want the corresponding cells, E*:G* to remain blank
    <LI>If C* has a value then I want a portion of the value in C* distributed across the corresponding cells E*:G* according a percentage table I have already created. 100% of C* will be distributed.
    <LI>Column C is for recording deposits
    <LI>Column D is for recording expenses
    <LI>Colums D:G are for recording and totalling the deposits and expenses of the various categories[/list]Currently, I solve this problem by putting a formula in each cell, E*:G*, that looks something like this:

    =IF(C1="","",C1*Jan_Restock) {Jan_Restock refers to a cell in a table that has already been created}

    This solution works fine if the user follows the correct procedure. My concern is that someone will put a value in one of the cells, E*:G*, and erase the formula that is there. This could happen either because the user made a mistake or started to distribute an expense and then decided to use the line as a deposit, which would erase the formula.

    Eliminating this danger would require the ability to monitor the value of C* and assign a value to cells E*:G* from a remote location when appropriate. I have not worked in Excel in a long time but I seem to remember a 'put' function of some kind that would allow you assign the value to a remote cell. I cannot find any such thing now.

    There must a reasonably elegant solution to this. I meant to bring a sample of the worksheet to the office to attach to this but failed to do so. But perhaps someone knows of a simple solution just from my description.

    Thanks for your help.

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    Is there anything preventing you from protecting the cells that contain formulas?
    Then unprotect cells that the user is allowed to manipulate, and protect the workbook.

    HTH

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Spokane, WA, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    Yes there is. When the entry is an expense one or more cells containing formulas will be over written as the expense is entered in the appropriate category/column.

    I have attached a sample of the worksheet so that anyone who is willing to try and help me can see what I have done. If you click on G8 you will a formula like this:

    =IF($D8>0;$D8*Percentages!C$3;"")

    There is one like this in each cell of F8:L8. Each is linked to the table in the Percectages tab. To avoid the potential problems I stated in the original post I am trying to find a way to accomplish this without putting the formula in the cell itself.

    I am developing this for a friend to keep some of her records for her cosmetic business. Eventually it will have a tab for each month of the year. The beginning balance for each month will be the linked to ending balance of the previous month. As the product develops there a number of other things we want to do with it. But first, I want to solve this problem.

    I have thought of writing a macro or something in VB but I am not proficient in either of these.

    Again, thanks for any help that you can give me.

    PS: I have written the formulas as they are so that the page is not cluttered with repetitive figures. This way data only shows when something has been entered in that row. If someone can show me a more elegant way to accomplish this, please do so.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    I'll take a look at your spreadsheet and get back to you.
    Until then, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>!

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    Aargh! I've been looking at this all weekend, and have been unable to come up with what you want. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> The merged cells caused me some headaches. I can understand why you'd want them - it would be helpful to see all of the description text. Know that I'm not giving up on you -- its just going to take longer than you're probably used to here in the lounge.

    Perhaps someone else has some ideas...

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    I don't know of a method to stop the over-typing your formulas, but you could put a check-sum at the end of the row and use conditional formating to highlight if things dont add up so that they can see they they have made an error. Then you just have to teach them how to use the "undo" key <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    HTH

    Peter

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Location
    Spokane, WA, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    As you can see, this has dropped down the priority list somewhat. Actually, some unexpected things have happened that jumped to the top of the list.

    Does the lack of a response reflect my absence or the fact that there may not be a solution to my problem? If the merged cells prevent solving the over-writing problem, I can find another way to deal with that situation.

    Would it make more sense to write something VB or develop something in Access?


    Thanks for your help.

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Location
    Spokane, WA, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using IF to put a value in a remote cell (XP)

    I already have a system in place to check for accuracy and completeness. It is the 'undo' process that I am trying to avoid. Most of the people who would use this are complete neophytes when it comes to computers. Teaching them to recognize this kind of error and correct it would defeat the purpose of a simple interface.

    Thanks for the suggestion though.

    Larry

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Using IF to put a value in a remote cell (XP)

    Hi Rudy,

    Having set up spreadhseets for use by clubs to manage their finances, what I think you really should do is to create separate worksheets for recording receipts and payments. That way, the formuale needed for the divvying up the receipts (and any other formulae you need to preserve) can be locked. If you need running totals, a third worksheet could be used to tally the other two. The ones I've designed have included facilities for bank account reconciliation and asset depreciation also, but all these functions are on separate worksheets.

    Even back in the days of XL 2.1, which didn't support multiple worksheets, I found it best to keep the various functions in different 'zones'. Navigation was a bit of a pain back then, but at least the users understood what went where and what they weren't allowed to change. Mind you, I have encounterd people who'd overwrite formulae with their own values and then complain the spreadsheet doesn't work!

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •