Excel coding and formulas for master and cost sheets

Hi,

I am just wondering if anyone can help me. I understand basic excel and have some understanding of formulas. But I have hit a stumbling block and wondering if what I am trying to achieve is possible and if so how?

Basically I am trying to create a simple costing excel spread sheet (I say simple but its proving far from it). Firstly I would like to create a master sheet of all prices of components which includes material costs, manufacturing costs etc. For example:

Description Material Labour Paint Total ex VAT VAT Total inc VAT
800mm x 900mm side panel £150.00 £30.00 £20.00 £200.00 20% £240.00

Above is a basic example of the information that would be on the master sheet. I would like to use this master sheet to create pricing sheets for projects and use coding as a simple way of getting the information from the master and placing it into the pricing sheet. (sorry if this isn't explained in the best way).

For example, I would like to have a code next to all of the items in the master sheet, eg A1, B1 etc... Then when I go into the pricing sheet I would like to type in the simple code eg A1 and for this to insert just the description and the total price of that component. (In total I will have 50+ components) In theory this will allow me to edit prices in the budget sheet for materials costs as and when I need to, and then within a new sheet be able to provide quick and easy pricing for customers.

I hope the information I have provided is sufficient enough for someone to tell me how in simple terms to achieve this, if it is actually achievable. Thank you in advance for your answers.

Hi Suzi
Please see attached sample which should be self explanatory.
Master sheet would contain all the items to be used on the pricing sheet.
When you click on a cell in column A of the Pricing sheet it will display a list of all the codes contained on the master sheet. When you select one all the relevant price data will be read into the Pricing sheet.
Hope this helps.
Good luck.
Tony

Hi,

Firstly thank you for your quick response.

I have downloaded the spread sheet that you have sent as an example and have a few questions, this may just be me not being a excel wizard. I have looked at the pricing sheet and when I have gone to type something in column A no options have appeared. I have then tried typing in a different code into column A for example A5 and whilst this has automatically entered data, this has entered the data that had already been inputted the first row under code A1.

Sorry if this is just me, but just wondering if you could give me a little more of an explanation.

Thank You in advance.