Making a table that auto populates based on adjacent cells

Hi,

I need to create an excel document which auto populates fields based on certain criteria.

The columns required will be light fighting, instruction, material cost, labour cost and finally total cost.

There needs to be a relationship so that depending on what light fitting is selected of which there are 9 or so different types each priced differently. Along with what instruction is selected of which there will only be 3 options…replace lamp replace light fitting or special each priced at fixed rates depending on the fitting.

An example would be I select a 28w 2d 4pin from a drop down list in cell A3 which needs just the lamp changed I would select the instruction replace lamp from a drop down in cell B3, with a material cost of £10 and a labour cost of £40 making the total cost £50, which would all need to auto populate in cells C3 to E3

An alternative would be the same as above but this time the whole fitting needs replacing which would be selected fro, the same drop down in B3 at a cost of £30 with a labour cost of £80 making the total cost £110 again all needing to auto populate in cells C3 TO E3

Any advice on how to make this work in theory with 9 different light fittings, I am not the most experienced with formulas or vba so apologies in advance. Any advice would be appreciated.

Thanks. Leo

With this type of problem you would normally use a VLOOKUP function or an INDEX/MATCH combination in conjunction with a table. The formula would find the appropriate match in the table and return the corresponding values.

It is difficult to advise you on an exact formula, however, without seeing how your table is laid out (presumably you have one, as you know that there are 9 different fittings, 3 different instructions etc.). Perhaps you can attach a sample Excel workbook which includes the table (which can be in its own sheet) and also shows how your main data is laid out. Click on Go Advanced while composing a post, scroll down to and click on Manage Attachments, then Browse and navigate to your file, double-click, then Upload | Close window | Submit post.

Hope this helps.

Pete

I have attached what I have laid out so far, I appreciate the complexity of the question and appreciate you taking the time to look at it, it has me stumped to say the least.

Thanks

Leo

I expected 4 columns of costs in your table on the Control sheet - one for materials and one for labour for replacement of the bulb, and one for materials and another for labour for replacement of the fitting (I'm not sure if you would also have these costs for "Special"). Could you oblige with another attachment?

Pete

I have all of the costs, bar a couple of the special costs, which I have just left as tbc, the remaining prices are fixed at the rates displayed in the attached spreadhseet.

Thanks

Leo

I've amended your table on the Control sheet slightly to make it a bit easier to get the values from it, and I've put data validation in columns D and E so that you can pick from the lists available, and I have this formula in F3:

=IF(OR(E3="",D3=""),"",INDEX('Control Sheet'!$B$3:$G$16,MATCH(D3,'Control Sheet'!$A$3:$A$16,0),MATCH(E3,'Control Sheet'!$B$1:$G$1,0)))

and this one in G3:

=IF(OR(E3="",D3=""),"",INDEX('Control Sheet'!$B$3:$G$16,MATCH(D3,'Control Sheet'!$A$3:$A$16,0),MATCH(E3,'Control Sheet'!$B$1:$G$1,0)+1))

(almost the same, except for the +1 near the end). I've also put this formula in H3:

=IF(AND(ISNUMBER(F3),ISNUMBER(G3)),F3+G3,"")

which will cater for the "tbc" returns for the two special options.

These formulae can be copied down, as required, as shown in the attached file.

Hope this helps.

Pete

That works great, thanks for that, I am going to have a play around with the formulas so I can see how each section works with each other and gain a better understanding.

Thank you again for you help

You're welcome - glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete