Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Update Prices (2003)

    I was asked yesterday by some people who keep price lists in Excel for a quick and easy way to increase all prices by a nominated percentage. It needs to be a solution they can implement themselves, and modify for different circumstances.

    The price lists do not have any consistent format, as some items might come in different sizes, and so have multiple columns etc.

    You could write a macro, but I thought that it would be very dependent on the layout of the sheet.

    My only other thought was to have a cell where you put the percentage, then for each cell with a price, a corresponding cell with a formula such as = b3 *(1+$f$1) to calculate the new price.
    Then they would have to Copy and Paste Special..Values to put the new prices back into the cells with the prices.
    Regards
    John



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

    Re: Update Prices (2003)

    You could do the following:
    - Enter the percentage + 100% in a cell, e.g. you want to increase prices by 5%, enter 105%.
    - Copy the cell to the clipboard.
    - Select the cells you want to increase.
    - Select Edit | Paste Special...
    - Click the Values and Multiply options.
    - Click OK.

    Note: instead of entering percentage + 100% in a cell, you can also enter the percentage in one cell, say A1, and enter the formula =1+A1 in another cell. Copy the latter to the clipboard and proceed as above.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Prices (2003)

    Thanks

    That works well.

    I have never noticed the "Operations" options in paste special.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Prices (2003)

    If the Price List was kept in a Word table, is there anything comparable you could do there?

    I can't see anything?
    Regards
    John



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

    Re: Update Prices (2003)

    Word is not a spreadsheet application. It is probably easiest to copy the table to Excel, update it, then copy it back into the Word document. Or embed an Excel table in the Word document; you can then double click it to edit it as if you were working in Excel.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Prices (2003)

    Thanks.

    These people are trying to work out which jobs are best done in Word, which In Excel, and which , if any, through embedding Excel in Word.

    For some of their lists they would like to include a picture of the item. A picture in a cell of a Word table is very stable.
    Excel wants pictures to be floating, as far as I know.
    Regards
    John



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

    Re: Update Prices (2003)

    Indeed, pictures in Excel are always floating. You can specify that a picture moves with the cell(s) it's floating over.

    If you want to update prices in a Word table and keep it user-friendly, you could provide a macro. But you'd have to be build in extensive plausibility and error checking.

Posting Permissions

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