Thread: matching and conditional referncing calculation...

Hi everyone,



I am working on this file where it needs reference from a seperate sheet to match a value and do conditional calculation based on the values it has on a different cell.



For example..

Sheet 1 Has manual Data..



Sheet 2. stores allocation and referencing Data



Sheet 3.. Needs to run the calculation.



So, From Sheet 3. match A1 against the A column on Sheet 2 if matched then caluculate the corresponsding value that matched from Sheet 1 based on the conditions applied on Sheet 2.



So for instance in the example attached:



B5(Sheet3)

Should be calclulated based on following condition.



B5(Sheet3) match A4(Sheet2)



then the corresponding value from Sheet1 = D31

should be calculated on sheet 3 using following condition:



Check if F4(Sheet2) Yes or Other

If Yes: then divide D31(Sheet1) by counting number of FLT occured on column D from (Sheet2) then times by value in Sheet1 say a1.



IF OTher: Divide D31(Sheet1) by counting number of HOUSE occured on Column D from Sheet2 and then times it by G4 on Sheet2.



Hope this makes any sense. I have been using If function but it gets bit confusing and plus its not allowing to do more than 2 or 3 conditions like referencing. Can one Exce Guru help on this would be greatly appreciated.



Sample File Attached.