Excel: Create Simple Cost Estimate for Video Monitoring - Dropdown, Sheets & Material Prices

I wish you a Merry Christmas.

I am thinking about making a quote, for example for video monitoring with a drop down list. I have three sheets and I think it will be clearer:

1. Cost estimate in which everything will take place (customer document)
2. KNRY in which the scopes of work will be listed
3. Materials with prices

I would like it to work on this principle. In the "materials" sheet I will change the names of materials and prices. This material will be assigned the scope of works and assigned to KNR from the sheet "KNRY". Entering the "estimate" sheet, I click on the first field under the name "list of works" and I would like a selection list to appear. I choose from the list, for example, "Mounting the GC-VP540IRC / ESD554 indoor camera" and automatically excel enter the appropriate KNR, and the price of the material and labor multiplied by the amount I give.

I don't know if it will be more convenient to do it on two sheets or on three. Waiting for your advice on how to bite it. I have already made estimates, I would like to modernize them.

After a while I wrote a different estimate, a preview in the file estimate1. I don't know if this way will be better. I made a drop-down list and I have a problem what functions to use to make it work.
For example, I select from the list in any line "installation of an internal camera" and the program will enter the appropriate KNR for it, in the "material" column it will multiply the price of the material times the quantity and the labor column to multiply the time norm times the amount times the cost estimate. Please tell me what functions to use. Best regards.

sanatarium wrote:

Exel

Pity, how many times you have to ask for the correct name of this program ...

I suggest using the simplest function index . enter position and ... normal functions multiply .

The whole can look like the example below.

protry..xls Download (25.5 kB)Points: 0.5 for user

Sorry for the typo in the name, I corrected. For now, thank you for your help. If I still need help, I'll write. Best regards.

I still have a question. I can't find anything about it on the internet. Can you format the text in the drop-down list?

sanatarium wrote:

Can you format the text in the drop-down list?

Sorry, I don't understand the question
format.JPG Download (144.16 kB)
As you can see in the screenshot, you can always format the cell into which you enter data from the drop-down list

I asked a little wrong. I will introduce the topic. Based on the "protrys" file on the right is a list of all the works that I divided into groups. Group names are listed in the drop-down list and separated by gaps, but I would like to see them in the format that I format.

sanatarium wrote:

I would like to see them in the format that I format

This is just not clear if you want to "format" it - literally, put some color and typeface or text alignment in the drop-down list - the answer is - NO!

But I feel through my skin that you mean something else? Look carefully at my previous dump - you can see what range ($ G $ 11: $ G $ 20) this drop down list has.

You can drag down the same drop-down list, you can change the range for the next cell, you can specify the range with a neat formula, e.g. by displaying only the first letter from any action list e.t.c

That's what I meant to put in the drop-down list some color or font type. It is a pity that this is not going to do. I will combine Thanks.