Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accounts Spreadsheet (2000)

    Hi,

    Looking for some assistance on this spreadsheet.

    Is there a way to do the following:

    I have a running total on the right hand side which keeps a running balance of the bank account. However when we issue cheques we enter them on the day that the cheque gets issued, but it would be days later or a week later that it gets cashed.

    In the BAC / Credit No column we use the following abreviations to indidicate the type of transactions:

    O/L - Online
    BAC - BACs payment
    123 - Indicate the cheque number

    We basically want to be able to only add the cheques to the running balance if the cell is right aligned and not left aligned.

    Is there a way to do this?
    Attached Files Attached Files
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounts Spreadsheet (2000)

    You have 2 columns labeled BAC / Credit No / Online. Does your question apply to both?
    And why didn't you enter a few examples in this column / these columns so that we could test?

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounts Spreadsheet (2000)

    Hans,

    Sorry for the delay, but have been out of action for a few days.

    I have re-done the spreadsheet with some sample data in.

    What we want to be able to do is to only deduct the cheques on the debit section of the spreadsheet if the cell value for BAC / Cheque / O/L or right aligned. If it is left alighned then it does not deduct it from the running balance.
    Attached Files Attached Files
    Best Regards,

    Luke

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Accounts Spreadsheet (2000)

    Does someone manually right/left align teh numbers?

    If so it would be much simpler to add a column and place an "x" in it to indicate to not include it. This could be done without requiring a user defined function in VBA to check the alignment. You could even use conditional formatting to mark the numbers to add (eg you could strikethrough the ones that are not included or put them in italics or grey them out, etc)

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accounts Spreadsheet (2000)

    I agree with Steve. Taking the alignment into account would require the use of VBA. If you add an extra column in which you indicate which entries should be included in the calculation, you don't need VBA.

  6. #6
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accounts Spreadsheet (2000)

    That seems to be the best solution to add a column to indicateif the total is to be added to the running total if there is an 'x' in there, then to to deduct it from it, if not then to not deduct it. How would I achieve this?
    Best Regards,

    Luke

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Accounts Spreadsheet (2000)

    How about something like the attached?

    I inserted a column O. In the Sum in Col P I added a conditional format that strikesthrough the sum if column O is not blank. In the formula in Col V I used a formula like:
    =SUM(V6+I7)-P7*(ISBLANK(O7))

    So that the value in Column P is only subtracted when Col O is blank.

    We could also explicitly search for "x", but this allows anything to be used to indicate an exclusion in case you wanted to use it for some comment on why excluded.
    Steve
    Attached Files Attached Files

Posting Permissions

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