Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of comments text for conditional values (Excel

    The task is to populate a budget sheet with potential revenue (or costs).
    Specifically, I work Monday, Wednesday and Friday mornings at the phenomenal rate of $200 per

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts

    Re: Use of comments text for conditional values (Excel

    Hi Chris,

    My first question is: Do you really need to have all this to-ing & fro-ing with values and comments?
    My second question is: Do you really need to show the $200 in the cells for the work dates?

    The reason I ask is that you can calculate the potential revenues with neither, and without any vba.

    The attached revision to your worksheet shows how this can be done. I've used conditional formatting to differentiate the potential workdays from the others. If a date is deleted, the cell changes colour and, if the date was a potential workday, the values on rows 34 & 36 change. The values on row 34 are calculated from a hidden worksheet, whereas the values on row 34 are calculated via an array formula that doesn't need the hidden worksheet. Protction (no password) is used to hide formulae and prevent users changing something they shouldn't.

    I also changed your basic formulae to make them more robust - in your version, deletion of a date caused all the others to change.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of comments text for conditional values (E

    Macropod, thanks for the prompt feedback and please accept my apologies for the late reply; some paid work reared its ugly head .....

    > Do you really need to have all this to-ing & fro-ing with values and comments?
    For myself, no. I'd use a piece of paper and a pencil; but the client wanted to populate a spreadsheet with dates, circulate the spreadsheet for comments, then convert the dates to dollar amounts based on Weekday=Mon/Wed/Fri.

    >Do you really need to show the $200 in the cells for the work dates?
    Similar answer to the above. The client wanted to display when specific amounts should arrive in the bank account.

    For my future work, I was interested in the possibility of saddling a cell with two useable values of different types. In this case I was toying with dates and dollar amounts. Future work could involve strings and dates, numeric and strings, and so on.
    I wasn't aware of anyone making use of comment fields to store values related rigorously to the visible cell contents.
    A further option would be to swap the cell contents with the comments.

    Yes, I was aware that my date generation was intact. The client received an edit-pastespecial-contents version!

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,888
    Thanks
    0
    Thanked 188 Times in 172 Posts

    Re: Use of comments text for conditional values (E

    Hi Chris,
    <hr>We begin by populating the (attached) spreadsheet with data values.<hr>
    What are these? The dates, the $200 values or something else?
    <hr>The financial values are to be transferred to cells B2:N32<hr>
    Again, what are these? The $200 values or something else? If something else, where do they come from?
    <hr>The first macro moves the cell contents to the comments text: The second macro uses the comment text to determine whether a value should be placed in the cell.<hr>
    It seems to me these processes could be done as a single procedure. Is there a reason you need to copy cell's contents to the comments first? Also, what if the cell already has comments - what would you do with them and how, if at all, would they impact the evaluation?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of comments text for conditional values (E

    > It seems to me these processes could be done as a single procedure. Is there a reason you need to copy cell's contents to the comments first? Also, what if the cell already has comments - what would you do with them and how, if at all, would they impact the evaluation?

    The particular exercise is a request. The user wanted to populate a spreadsheet with dates, circulate the sheet for comments, then map the dates into dollars, while retaining the dates as references. Hence the original dates would be shunted into the comments. And yes, any existing comments would be obliterated. There weren't any.

    I posted it here because it seemed to me that this was a general-solution to a problem that might be defined as "How to store two dis-similar but related data in each cell - in a way that the end-user can see both". A better approach would have been to provide a tool that swaps the comment with the cell. In my given example, the dates and dollar views would be toggled.

Posting Permissions

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