Excel 2007

Posted on 2011-05-04
Last Modified: 2012-05-11
I have a workbook, about 50 sheets, they all start at Line 9, finish at line 55. (Attached sample)
I need find in column B (TO) Values (J8Z,J9J,J8P,J8R,J8V,K2W,K4C),
If any of those postal codes are in column B, then increase the price --on that line---  in column C,D,E,F by 25$.
sheet 1
FROM      TO      Service 1      Service 2      Service 3      Service 4
Core      J8Z      $7.20      $8.10      $9.05      $12.95

will become
Core      J8Z      $32.20      $33.10      $34.05      $37.95

Can you please help.
Question by:Wass_QA
    LVL 31

    Expert Comment

    by:Rob Henson
    Rather than adding it in to your base price, why not add a surcharge column with a lookup based on the Post Code.

    Then setup a small data table with the postcodes that get a surcharge and the surcharge related to that postcode, in your example they are all the same but could they change??

    The formula for the lookup would be:


    I think thats the right syntax for IFERROR in 2007, machine I am sat at is only 2003.

    Rob H

    Author Comment

    Hi Rob,
    This is a good idea,
    but, yes, the postal codes will change, depends on the client, also, the rate will change.
    I was hoping, if there was a macro I can run,
    this way, everytime I have aa new client with special pricing, all I have to do is change the lookup postal codes, surcharge amount.

    LVL 31

    Expert Comment

    by:Rob Henson
    Assuming you have a separate file per client, change the amount in the lookup table and it will feed through into the data automatically.

    If all in the same file, the lookup area could have multiple columns against each Postal code and then change the column offset in the lookup depending on the client, dynamically by matching a client code if so required.

    Sounds like you need a data area with Service Charges for each different client, or client group, as well.

    Rob H
    LVL 31

    Expert Comment

    by:Rob Henson
    See example attached.

    Rob H Surcharges.xls

    Author Comment

    HI Rob,
    Appreciate your help,
    this will work, if I only have couple sheets, takes few minutes,
    but as I said, I have on this plan alone 50 sheets.
    and beside, if I do it this way, I still ahve to go back into each sheet, copy - paste special the values back into the main template.
    LVL 31

    Accepted Solution

    Assuming existing data in columns A - F, add the surcharge lookup formula in column G row 9 and copy down to row 55 on sheet 1. Select the newly populated cells and copy to clipboard.

    Select sheet 2 G9 and then scroll the tabs until you can see the tab for sheet 50, press shift and select sheet 50; sheets 2 to 50 now grouped. Paste from clipboard and cells G9 to G55 on all sheets will be filled with the formula. Select only sheet 1 and it will ungroup the sheets.


    Author Closing Comment

    Thanks Rob,
    I was a bit confused with you adding an extra column,
    I had to add this column, and add Special 1 into it,
    but in all, it worked.

    Thansk again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now