2007 formula help please

[Guest]

I have a workbook with 100 sheets. Each sheet contains 50 or so items and
pricing for each item. I need to updat the price list on each sheet based on
a single master sheet. I was using straight percentages from a master pay
sheet but am now forced to use multiple percentages per sheet. What I need
is to write a formula that based on a cell value on the master sheet the
pricing for each item will update from another workbook or workbooks.

example: workbook = weekly totals
sheet 9500 thru 9600
cells E5 thru E55
update from workbook "80% payout" Cells E5 thru E55
when I change workbook = weekly totals
sheet Employees
cell H31 to 80% or would update from workbook "60% payout" if I put 60% in H31

Please help Thank you

 

[Roger Govier]

Hi

If there is a Master price List, then no other sheet should have hard
coded prices on it. They should all be Vlookups from the Master sheet,
then a single change to the Master would update all subsidiary price
lists.

Suppose on your Master sheet you have in columns A and B
Product Price
ABC 100
DEF 120
etc.

Supposing also, the other sheets are set out in a similar way. In cell
B2 of each other sheet enter
=VLOOKUP(A2,'Master Sheet'!$A:$B,2,0)
and copy down for the 50 or so rows required

Change the references to the locations applicable for your sheet layout.

For more help on VLOOKUP take a look at
http://www.contextures.com/xlFunctions02.html