Excel 2012 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | 2011 | |||||||||||||
3 | ||||||||||||||
4 | ||||||||||||||
5 | ||||||||||||||
6 | COMPANY | |||||||||||||
7 | ||||||||||||||
8 | ||||||||||||||
9 | ||||||||||||||
10 | Component | |||||||||||||
11 | ||||||||||||||
12 | ||||||||||||||
13 | ||||||||||||||
14 | Non-Current Assets | |||||||||||||
15 | ||||||||||||||
16 | $'mil | $'mil | ||||||||||||
17 | Property, plant and equipment | |||||||||||||
18 | Intangible assets | |||||||||||||
19 | Subsidiaries | |||||||||||||
20 | Deferred tax assets | |||||||||||||
21 | Balances with related parties | |||||||||||||
22 | ||||||||||||||
23 | ||||||||||||||
24 | ||||||||||||||
25 | ||||||||||||||
26 | ||||||||||||||
27 | ||||||||||||||
Report |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | =INDEX('Master Data'!C53:C56,'Master Data'!H53,1) | |
D6 | ='Master Data'!G61 | |
B14 | =INDEX('Master Data'!C64:C68,'Master Data'!G64,1) | |
B17 | =IF($B$14='Master Data'!$C$78,'Master Data'!C79,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G79,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J79,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M79,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R79," "))))) | |
B18 | =IF($B$14='Master Data'!$C$78,'Master Data'!C80,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G80,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J80,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M80,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R80," "))))) | |
B19 | =IF($B$14='Master Data'!$C$78,'Master Data'!C81,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G81,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J81,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M81,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R81," "))))) | |
B20 | =IF($B$14='Master Data'!$C$78,'Master Data'!C82,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G82,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J82,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M82,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R82," "))))) | |
B21 | =IF($B$14='Master Data'!$C$78,'Master Data'!C83,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G83,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J83,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M83,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R83," "))))) | |
B22 | =IF($B$14='Master Data'!$C$78,'Master Data'!C84,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G84,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J84,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M84,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R84," "))))) | |
B23 | =IF($B$14='Master Data'!$C$78,'Master Data'!C85,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G85,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J85,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M85,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R85," "))))) | |
B24 | =IF($B$14='Master Data'!$C$78,'Master Data'!C86,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G86,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J86,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M86,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R86," "))))) | |
B25 | =IF($B$14='Master Data'!$C$78,'Master Data'!C87,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G87,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J87,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M87,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R87," "))))) | |
B26 | =IF($B$14='Master Data'!$C$78,'Master Data'!C88,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G88,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J88,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M88,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R88," "))))) | |
B27 | =IF($B$14='Master Data'!$C$78,'Master Data'!C89,IF(Report!$B$14='Master Data'!$G$78,'Master Data'!G89,IF(Report!$B$14='Master Data'!$J$78,'Master Data'!J89,IF(Report!$B$14='Master Data'!$M$78,'Master Data'!M89,IF(Report!$B$14='Master Data'!$R$78,'Master Data'!R89," "))))) |
There is supposed to be a scrollbar beside the year and it ranges from 2011-2014, combo box beside the company which i can change to either group or company, and component. Basically, i want to retrieve my data in cell J17 from the next picture. I want to set a formula so that whenever i changes something, example, the company is changed to group, year changes etc, my data will also change accordingly. Hope someone can help me out with the formula please. Thanks in advance!
Excel 2012 | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
3 | GROUP | COMPANY | |||||||||||||
4 | 2011 | 2012 | 2013 | 2014 | 2011 | 2012 | 2013 | 2014 | |||||||
5 | $mil | $mil | $mil | $mil | $mil | $mil | $mil | $mil | |||||||
6 | Non-Current Assets | ||||||||||||||
7 | Property, plant and equipment | 761.9 | 791.1 | 857.4 | 911.1 | 344.9 | 367.7 | 384.1 | 400.5 | ||||||
8 | Intangible assets | 423.6 | 397.0 | 380.6 | 404.5 | 126.9 | 105.9 | 92.1 | 79.3 | ||||||
9 | Subsidiaries | 1,202.1 | 1,202.1 | 1,692.1 | 1,692.1 | ||||||||||
10 | Deferred tax assets | 2.8 | 2.5 | ||||||||||||
11 | Balances with related parties (Assets-Long-Term) | 0.0 | 0.0 | 0.0 | 600.0 | 123.0 | 106.0 | ||||||||
12 | Total Non-Current Assets | 1,188.3 | 1,190.6 | 1,238.0 | 1,315.6 | 1,673.9 | 2,275.7 | 2,291.3 | 2,277.9 | ||||||
13 | |||||||||||||||
14 | Current Assets | ||||||||||||||
15 | Inventories | 37.2 | 28.1 | 43.2 | 42.4 | 29.0 | 21.2 | 36.7 | 36.8 | ||||||
16 | Trade receivables | 152.0 | 142.3 | 123.5 | 161.7 | 139.9 | 130.9 | 108.5 | 147.7 | ||||||
17 | Other receivables, deposits and prepayments | 149.2 | 123.6 | 154.4 | 185.9 | 22.0 | 20.4 | 29.8 | 34.0 | ||||||
18 | Balances with related parties (Assets-Short-Term) | 17.2 | 12.0 | 24.0 | 17.4 | 661.6 | 48.2 | 111.3 | 272.6 | ||||||
19 | Cash and bank balances | 179.2 | 312.0 | 266.9 | 264.2 | 154.3 | 245.7 | 207.5 | 176.0 | ||||||
20 | Total Current Assets | 534.8 | 618.0 | 612.0 | 671.6 | 1,006.8 | 466.4 | 493.8 | 667.1 | ||||||
21 | |||||||||||||||
22 | Current Liabilities | ||||||||||||||
23 | Trade payables and other payables and accurals | 702.9 | 728.1 | 753.7 | 795.8 | 353.4 | 344.5 | 335.3 | 342.9 | ||||||
24 | Balances with related parties (Liabilities-Short-Term) | 41.9 | 56.5 | 76.5 | 99.2 | 296.0 | 321.5 | 402.8 | 385.6 | ||||||
25 | Bank Loans (Short-Term) | 75.0 | 200.0 | 75.0 | 200.0 | ||||||||||
26 | Provision for taxation | 83.5 | 94.6 | 71.4 | 97.9 | 55.7 | 51.4 | 35.0 | 35.5 | ||||||
27 | Total Current Liabilities | 903.3 | 879.2 | 901.6 | 1,192.9 | 780.1 | 717.4 | 773.1 | 964.0 | ||||||
28 | |||||||||||||||
29 | Non-Current Liabilities | ||||||||||||||
30 | Bank Loans (Assets-Long-Term) | 587.5 | 587.5 | ||||||||||||
31 | Other payables | 34.1 | 37.5 | 28.9 | 23.3 | 34.1 | 37.5 | 28.9 | 23.3 | ||||||
32 | Borrowings | 687.5 | 687.5 | 487.5 | 687.5 | 687.5 | 487.5 | ||||||||
33 | Deferred income | 62.8 | 41.7 | 21.3 | 7.3 | ||||||||||
34 | Deferred tax liabilities | 112.8 | 119.2 | 128.0 | 127.2 | -69.8 | 72.4 | 75.8 | 75.9 | ||||||
35 | Total Non-Current Liabilities | 797.2 | 885.9 | 865.7 | 645.3 | -691.4 | 797.4 | 792.2 | 586.7 | ||||||
36 | |||||||||||||||
37 | Net Assets | 22.6 | 43.5 | 82.7 | 149.0 | 1,209.2 | 1,227.3 | 1,219.8 | 1,394.3 | ||||||
38 | |||||||||||||||
39 | Shareholders Equity | ||||||||||||||
40 | Share capital | 262.8 | 263.1 | 271.4 | 282.6 | 262.8 | 263.1 | 271.4 | 282.6 | ||||||
41 | Reserves | -240.2 | -219.6 | -188.7 | -133.6 | 946.4 | 964.2 | 948.4 | 1,111.7 | ||||||
42 | |||||||||||||||
43 | Total Equity | 22.6 | 43.5 | 82.7 | 149.0 | 1,209.2 | 1,227.3 | 1,219.8 | 1,394.3 | ||||||
Master Data |