+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Formula that spans 2 Workbooks for retail prices

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula that spans 2 Workbooks for retail prices

    Hi all,

    I am starting a new business and need some help setting up my pricing.

    I'm a little new to excel, and have spent the day today looking through this forum and learning as I go. Forgive me please if I use the wrong terminology.

    Here is what I have:
    I have one workbook that lists all my COST prices, spread over a few worksheets. Then I have a second workbook that has all my RETAIL prices, also spreed over a few worksheets. (Both files are in the same folder).

    Here is what I will do:

    Each month I will do a review of our COST workbook, and amend the price changes as necessary. But what I would like is for these changes to reflect automatically in the RETAIL Workbook.

    We will have a set MARGIN of 20% that needs to be added into the equation. This number may change up or down (depending on how business goes , and needs to be easily amended, so I think it need to be in a cell, and just referenced by the formula. That way I could change it, and the RETAIL Workbook could be updated automatically too.

    Formula?

    So here is the formula that I think that I need:
    COST (Cell in workbook one) A1
    ADD
    MARGIN Percent (Cell in workbook two) B1
    Equals
    RETAIL (Cell in workbook two) C1


    Could somebody help me to:
    • Write the formula
    • Show me where to enter it in the worksheets

    Any help would be much appreciated.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Need a Simple formula that spans 2 Workbooks for retail prices

    Hello reganfrank,

    welcome to the forum.

    You can reference a cell in another workbook easily: With both workbooks open, click the cell in the target book, type a = sign, then click the source workbook, navigate to the source cell, click it and hit Enter.

    You can also do that while typing a formula. So start your formula, and where you need the reference to the external workbook, just click it.

    But I think your issue will be a bit more complex than that. Unless your Cost and your Retail workbook have exactly the same structure, a straightforward cell link will not work for long. What if your range of products changes? If you need to insert a row in the Cost sheet, then the references in your Retail sheet may get broken.

    A better approach would be a lookup formula, that finds the required product by a unique ID.

    Long story short: It would be much better if you could post a data sample of your two books, to see how a references across sheets can best be set up in a robust and maintainable way. Post a shortened version of your Cost and your Retail file with your current data structure.

    cheers

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Need a Simple formula that spans 2 Workbooks for retail prices

    Hi,

    You might want to post a dummy set of workbooks that look like the final workbooks that you will be using. It would also be preferable if the names are the same as the ones you will be using.

    This will be helpful for us to understand exactly what it is that you need.

    Good luck,

    abousetta

  4. #4
    Registered User
    Join Date
    04-30-2010
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula that spans 2 Workbooks for retail prices

    Thanks for your quick response.

    Here are the two samples.
    Last edited by reganfrank; 05-01-2010 at 04:39 AM.

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula that spans 2 Workbooks for retail prices

    First piece of advice: lose your merged cells. They will cause nothing but trouble down the road. Use Center across selection if you want text to run in more than one column.

    Second: What is the connection between the two workbooks? Do you actually expect anyone here to figure it all out on their own?

    How about giving a few mocked up results and explaining how you got there. The two workbooks have completely different structures, so you must provide the logic that leads to your result, and also where to find the numbers that go into your grid in the first place.

    Your values in the Retail example are text. From your initial description above, I figured that you want to do some kind of calculation.

    All in all, there's not enough information here to provide anything.

    Over to you.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Formula that spans 2 Workbooks for retail prices

    Why use two workbooks & why have data spread over multiple sheets.

    In addition,your data layout completely ignores the standard layout for data. basically all data should be in a table format, i.e a header row with no completely empty rows or columns. Set the data up correctly and you have access to Excel's powerful database tools for working with that data
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    04-30-2010
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula that spans 2 Workbooks for retail prices

    Thanks abousetta, teylyn and royUK for all your feedback.

    You guys have given me much to think about. I guess I need to rethink this a little.
    Will get back to you guys with some thoughts later...

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1