Master formula copied from one worksheet to another

[halaville]

I have a workbook that contains many worksheets. Each worksheet is a customer's price list. There are many formulas in the worksheet that are duplicated from one worksheet to the next.

I would like to create a master worksheet and build up the formulas in specific rows and columns. Then, can I have the rest of the worksheet refer to the formula in the master and return a value based on the specific worksheet not the master?

Goal, if I need to modify a formula; I will only update the master and the rest of the worksheet will automatically update the formulas without copy and pasting? Is it doable?

 

[SkipVought]

Hi,

Your hypothetical, vagueness is unclear.

Yes, it is possible and in most cases desirable, to have all your similar data on one sheet. However, it must be properly structured to provide the best results and leverage the features of Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[halaville]

For example, sheet1 will have a formula in cell F1 = D1*E1
I would like to put a formula in sheet2 that refers the formula in sheet1 but actually return based on values in sheet2.

Sheet1
D E F
1 3 2 6 (cell F1 is a formula (=D1*E1)

Sheet2
D E F
1 5 1 5 (cell F1 is a formula that looks up sheet1.F1 but returns value based on sheet2)

 

[SkipVought]

Unless row 1 on EACH OF your scattered sheets is always equivalent to row 1 on the master, which I seriously doubt, your method of 'lookup,' which it is not, will be VERY labor intensive and error prone.

What you may want is a LOOKUP function of some sort, depending on how your data is structured. You apparently have no column headers, referencing row 1 as data. In a proper table structure, row 1 is headers, that describe the kind of data in that column, like Name, Amount, Region etc.

Please describe your data that resides in each of your customer sheets.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

In many cases it is actually easier and better to have all your source data in 1 sheet. To achieve this all you would really need to do is to add the customer name / ID to your data and append each set of data to 1 main sheet that way, it is very easy to ensure that all formulae are propogated and correct

It is also very easy to then run a short macro to create a specific customer price list dynamically

In other words, do not confuse the end state of your data from the state in which it should be kept / maintained - the 2 are often quite different and trying to store data in it's output format can cause a lot of difficulties (as you are running into)

Please give serious consideration to what would be quite a minor design change which could give you a lot better structure and maintainability to your information

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[halaville]

SkipVought/xlbo: Yes, the data in each row is different within the worksheet and from one worksheet to the next. Each worksheet is a customer's price list, where it lists the customer's price and weight for each product. Actually I do have headers, hopefully this explains it better!

Customer ABC:
Weight $/# Sale Price
Product 1 3 $2 $6

Customer XYZ:
Weight $/# Sale Price
Product 2 5 $1 $5


Column D is weight in pounds
Column E is unit price per pound
Column F is the extension or sale price (weight x unit price)
Row 1 is the product name

The rows and columns headers are consistent from one worksheet to the next. So, I thought instead of copying and pasting the formulas for all the worksheet; if there is a way to make a master formula worksheet and some how refer it but based on the data from the actual worksheet. That way, if the computation change, I want to be able to update the master formula and it would update the rest automatically! I imagine Bill Gates must have thought this idea and Excel ought to be capable of doing it!

I am unable to have all source data in one sheet, because the customers are buying various products at different prices (even the same product might be at different prices).

Thank you so much for your feed back.

 

[SkipVought]

Your master sheet, then, ought to be structured...
[tt]
CUSTOMER PRODUCT WEIGHT Unit_Cost Sale_Price
Customer ABC Product 1 3 $2 =WEIGHT*Unit_Cost
Customer XYZ Product 2 5 $1 =WEIGHT*Unit_Cost
...

[/tt]
using Named Ranges, where the headers are in row 1, and the Sale_Price is calculated.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI ---

faq68-5184

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Using Skip's layout above, it would be the work of a couple of minutes to set up a process to generate a price list of any one (or multiple) customers

It would also be possible to use another sheet as a pricing matrix where you could set up customers against different price points for different products and a 3rd sheet that holds the price per product per list - that way you can very easily move customers into different price bands / lists or even update the prices for a specific list and have it update all customers on that list

Unit_Cost formula:

=VLOOKUP(VLOOKUP(Customer & Product,Price_List_Lkup,2,false) & Product,Product_Cost_Lkup,2,false)

[green]-- This does require a "helper" column which is a concatenation of Customer & Product / Price List & Product in each of the lookup areas [/green]

[tt]
CUSTOMER PRODUCT WEIGHT Unit_Cost Sale_Price
Customer ABC Product 1 3 =Unit_cost =Weight*Unit_cost Customer XYZ Product 2 5 =Unit_cost =Weight*Unit_cost [/tt]



[tt]
CUSTOMER PRODUCT HELPER Price_List
Customer ABC Product 1 =Customer&Product a
Customer ABC Product 2 =Customer&Product b
Customer XYZ Product 2 =Customer&Product b
[/tt]

[tt]
Price_List PRODUCT HELPER Unit_Cost
a Product 1 =Price_List&Product $2
a Product 2 =Price_List&Product $1
b Product 1 =Price_List&Product $3
b Product 2 =Price_List&Product $2[/tt]

Once set up, all you would need to do is amend your product/price list values and / or customer / price list values to make changes to your master customer / product price lists.

A very simple macro would then enable the extraction of the data from the master sheet to a tmeplate Individual Price List sheet which could be set up to look good for printing / mailing

Your overhead to maintain everything from that point on would be mimimal


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Skip and Geoff are giving you great advice. But going back to your original post:

Goal, if I need to modify a formula; I will only update the master and the rest of the worksheet will automatically update the formulas without copy and pasting? Is it doable?

Excel does give you the ability to enter values or formulae into several cells at the same time. These cells can be in different worksheets.
Simple example:
Select all sheets
Select cell D2
enter the formula =B2*C2 press enter

More advanced example:
Select all sheets
Select cells D210
enter the formula =B2*C2 press Ctrl-Enter


Gavin

 

[tbihn]

I hope I'm not hijacking this thread (since it is only a month old), but I have the exact same question as the original poster. I have a predefined parameter spreadsheet setup to read in a test sequence. It has two tabs. One tab is labeled "Bay1" and the other tab is labeled "Bay2". The test software that reads this is locked down, so I am limited to my ability to change the structure of the spreadsheet. They have this setup for flexibility so that you can have different tests in different cells, but it creates a problem...There is no way to guard against an update a sequence in the "Bay1", but don't update "Bay2". The ONLY difference between these two sheets is that "Bay1" references "Zebra1" and "Bay2" references "Zebra2". The way this spreadsheet is structured, you can add delays and other test sequence steps by moving rows around. I'd like to setup a formula in one sheet that references the sheet name (to extract the 1 or 2) and in the other sheet, use that formula to get the sheet name.

Is this actually possible without a macro? I want to set this up so that "Bay2" is locked and if someone wants to make a change, it only occurs on the first sheet

 

[SkipVought]

but I have the exact same question as the original poster.

Then post your question in a new thread. If your question was the 'exact same question' then you would alredy have an answer from this thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tbihn]

I apologize, I will copy and paste to a new thread. It didn't appear to me like the op's question was actually answered, but instead redirected to something the responders actually knew. My gut tells me that nobody here will know the answer to indirectly referencing a formula, but will instead try to solve the problem in a different way such as was evidenced in this thread.

 

[xlbo]

there is no way to "indirectly" reference a formula - that's why we were suggesting different methodologies (aside from the point of view of better design)

you can try to be very clever and have indirect range pointers e.g. I can put A1 in cell F1 and A2 in cell F2 and use =INDIRECT(F1)*INDIRECT(F2) which will multiply A1 & A2 but tbh I don't think this is what is wanted and it only serves to obfuscate what the calc is trying to do

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question