Look up word then multiply by number

Hi

Hoping someone can help me

Im looking to produce a formula

I need a drop down box in J19 with options

Monthly commission £2
Monthly commission £2.33
Monthly commission £2.55

Then the below formula will calculate the number of carbon tonnes and multiply it by the monthly commission amount chosen in the drop down box and put it in the correct pay period. See below

To fill in cell J21 i want the formula to look up Column G the words Period 9. Any rows which contain Period 9 need the Cell D (Carbon Tonnes) on the same row to be multiplied by the number in the drop down box in J19 which will give me my answer

If someone could let me know how I can do it that would be great. I think it would need something to

Tester.xlsx

Hi,

Please see the attached file, I have updated the formula.


J20 = IFERROR(INDEX($A$1:$G$3,MATCH(I20,$G$1:$G$3,0),4)*(1*(MID($J$19,SEARCH("£",$J$19,1)+1,4))),"") and drag down

Hi Boopathiraha Thanks for the help. Its almost right but the sums don't work

Period 9 on monthly commission at £2 should show 20 carbon tonnes x £2 = £40 but yours show £4800?

Hi cbatrody

Thats it. Works perfectly. Thank you

Thanks Glenn Kennedy also it works

You are welcome

I guess even Glenn's template as well works.

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, I would like to inform you 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.

Hi,

Please see the updated file. I have modified the formula in J20.

=SUMPRODUCT(--($G$2:$G$2000=I20),--($D$2:$D$2000))*(1*(MID($J$19,SEARCH("£",$J$19,1)+1,255)))

Thanks both of you. Your both helping LOADS!

When I clear the contents of the cells in order to input the actual data it seems to wipe the formulas

I've tried protecting the sheet and formulas but it locks the cells completely. I need the formulas locked but an allowance to edit the data put into them?

Did you check the file I uploaded in my last post?

Yeah that worked cbatrody but when i cleared the dates and carbon tonnes it then would not work so i assumed it was clearing the data too?

Yes, it works based on column D & column G data. When you clear those columns, the results will be blank.

Can the formulas be locked but the content to be editable as thats what i want? Someone will type data in and clear it at the end of the month and all formulas will be erased?

Yes, that's possible, you need to lock the cells with formulae and protect the sheet with a password.

Hi Glenn

Yeah I was clearing G collumn as well but when i leave the collumn alone its fine now

Is there still anyway to lock the formulas down so they cant be accidentally edited or deleted? I just want it as a database where information can be put into

A-F and then the relevant formulas will work it out for you

Please select the cells to be locked (cells with formulae), then follow the procedure mentioned in the following URL

https://www.computing.vt.edu/kb/entry/1913

Tried what you both said and it dosn't seem to work

I have highlighted A-F cells and put in the allow users to edit ranges and an additional J19

Then protected the worksheet but it still lets them play with the formula in Collumn G?

Please see the attached file, I have hidden & Locked the cells with formulae and password protected the sheet.

password: abcd

Thanks. Thats perfect. Not sure how you managed to do it but it works