Newbie - please help!!

rockatubc

New Member
Joined
Jan 28, 2005
Messages
19
Hi,

From my browsing of these boards, I am certain that one of the more enlightened members can help me with my problem. Here is my problem:

1. I have 3 stores - all with the same products but with varying price differences to the base price (due to store location)

2. I have one worksheet that has all the sales/cost of goods/price differentials of each of the three stores forcasted out 5 years.

3. Each store has its own worksheet - I use the offset function to pull values from the master data sheet. Pulling sales volumes and costs is not a problem...

4. The problem is pulling sales prices.....there is one last "pricing" worksheet that has a base price for products and adjusts this price for the store differentials found in the master data worksheet. However....obviously this is static and can calculate only one differential at a time ( I put the number 2 in the top left hand corner, the worksheet recognizes that this is store number 2 and pulls the price differentials for store 2 from the master data sheet)....however, since all 3 stores are linked to the same pricing sheet, all thee stores get the same pricing differentials.

phew...

My question: Is there a way, other than copying the pricing sheet 3 times over for the number of stores, to somehow from each store worksheet, "force" the pricing sheet to spit out correct differentials depending on the store sheet I am on....

i am sorry if this is confusing but please help..

thank you very much
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
Load the Colo HTML maker at the bottom of the page and post some example data so i can more clearly understand. It sounds very workable, but still alittle unclear of what you desire.
 

rockatubc

New Member
Joined
Jan 28, 2005
Messages
19
Hi,

thanks for your reply - i really can't post the information b/c it is confidential. However, please tell me what u need me to clarify and i'll try my best....
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

Would something like this do?:
Book1
ABCDE
1StoreStore2StoreDifferential
2ProductWidget-B15-1Store15%
3BasePrice$15.00Store26%
4Adjustedprice$15.90Store37%
Sheet1


Formula in B4 is: =(1+VLOOKUP(B1,D2:E4,2))*B3

Hope that helps!
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
If you won't give a sample of the sheets, at least give examples written out and what you want to get.

IE: Washington, Dallas, New York (3 places)
Dolls, rubix cubes, GI Joes, Balls, Blocks (6 toys)
Prices per city written out how you have it now on each sheet with prices from each city, etc. This will help us help you!!!!!!!!!!!!!


Michael
 

rockatubc

New Member
Joined
Jan 28, 2005
Messages
19

ADVERTISEMENT

Ok - i've tried to illustrate an example below:

1st worksheet is an input page with my store's inventories and prices forcecasted out.
book2
ABCD
21InglewoodTotalInventoryPrice
320043$6.50
420054$6.76
520065$7.03
620076$7.31
720087$7.60
820098$7.91
920109$8.22
10201110$8.55
11201211$8.90
12201312$9.25
13
142LarocheTotalInventoryPrice
1520043$6.50
16200516$3.57
17200617$3.71
18200718$3.86
19200819$4.01
20200920$4.18
21201021$4.34
22201122$4.52
23201223$4.70
24201324$4.88
25
263FileezTotalInventoryPrice
2720043$1.10
28200528$1.17
29200629$1.24
30200730$1.31
31200831$1.39
32200932$1.47
33201033$1.56
34201134$1.65
35201235$1.75
36201336$1.86
Store Inputs
 

rockatubc

New Member
Joined
Jan 28, 2005
Messages
19
Store income statement - each store has its own:
book2
ABCDEFGHIJKLMNOPQ
1STORENUMBER1
2
32004200520062007200820092010201120122013
4Products3456789101112**IUSETHEOFFSETFUNCTIONTOPULLTHE
5Pricing$17.04$17.96$18.92$19.94$21.01$22.14$23.32$24.57$25.88$27.27INVENTORYFROMTHEFIRSTPAGE
6Income$51.1271.82894.612119.63147.06177.08209.89245.7284.72327.2PRICESDIRECTLYLINKEDTOPRICINGTAB
Inglewood


The main problem I am having is that one can see that the same "net price" is being applied to each store. How can I automate this so that depending on the store worksheet I am using, it is using the correct store differential and therefore getting the right net price. I know I could probably have 3 seperate pricing tables and pull seperately - but I am dealing with 250+ stores and that may get extremely complicated.

Please help again!!!
 

Forum statistics

Threads
1,148,222
Messages
5,745,467
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top