Linking a master price sheet to workksheets

[MR64]

I am a contractor and use excel to bid projects: i.e. decks
The spreadsheet that i created has every item listed that I could use in building a deck and currently I just enter the quantity amount and get a result based on the price of an item that is already listed. The problem I come across a couple of times a year is that the lumber prices change and I then have to go to each sheet and update. I would like to have a master sheet that I can manipulate the addition of rows, columns, prices, etc. and then have those changes be reflected on the bid sheets that i currently have AND any other sheets that I add in the future. If possible can you lock a certain sheet so that it cannot be changed when the master sheet is updated? I ask this so that if I have given a customer a bid last week when prices were let's say $10 and now they are $13 I want to honor the initial bid for a certain period of time.

Thank you in advance

Mark

 

[bgoree09]

Good afternoon,

What I would suggest is that you assign a start and end date for the prices. You can then call the right price with a sumif function or sumproduct (I'm much more familiar with sumif, but it doesn't work on closed worksheets. However, I believe sumproduct will work on closed sheets). It would look something like this:

MASTER

Item Start Date End Date Price
Lumber 1/1/2000 2/10/2014 10
Lumber 2/11/2014 12/31/2999 13

BID SHEET

Item Qty. Price
Lumber 7 =sumifs(**list of all prices**,**List of all Items**,**Cell Lumber is in (ex: a2)**,**List of Start Dates**,"<="&**Date of Bid, a cell preferably**,**List of End Dates**,">="&*Date of Bid**)

Basically, all this stuff above says: give me the sum of the price of lumber, where the bid date is between the start and end dates. The only quirk with this is that you will have to have the master open in order to get figures, because sumif will not work on closed documents. Someone more intelligent may be able to convert it to a sumproduct formula for you though :smile:. Also, in this example, when lumber goes up again, you would overwrite the end date of 12/31/2999 with the actual end date and then make your new entry.

Best of luck,

 

[MR64]

Should have added in my original post: using excel 2010 and have added an attachment of what my current file looks like.

I know I can go cell by cell and link it to the corresponding cell in the master, but is that the easiest / only way?

Thanks again

 

[bgoree09]

Hello again,

I took the first section and set it up similar to how I described earlier. I didn't adjust the structure of the document, but if you wanted to you could have a very simple inputs sheet and then run a pivot table off of it to create a nice clean report to give to the client if needed. This would slim the formulas down and save some work on the set-up side and potentially future maintenance, but even with this format the setup would essentially be a one time thing. I've highlighted everything I've done in red. I've thrown a date in where it's easy to see for reference. If you change this date, the prices will change accordingly.

Hope this helps,