Excel 2007 : Formula that spans 2 Workbooks for retail prices

**reganfrank** · 05-01-2010, 12:25 AM

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.

**teylyn** · 05-01-2010, 12:46 AM

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

**abousetta** · 05-01-2010, 12:50 AM

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

**reganfrank** · 05-01-2010, 01:11 AM

Thanks for your quick response.

Here are the two samples.

**teylyn** · 05-01-2010, 01:50 AM

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.

**royUK** · 05-01-2010, 02:12 AM

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

**reganfrank** · 05-01-2010, 04:37 AM

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