Extraction of Data (changes with text in combobox and scrollbar)

C

cleon96

New Member
Joined
May 17, 2015
Messages
7

Excel 2012
BCDEFGHIJKLM
22011
3
4
5
6COMPANY
7
8
9
10Component
11
12
13
14Non-Current Assets
15
16$'mil$'mil
17Property, plant and equipment
18Intangible assets
19Subsidiaries
20Deferred tax assets
21Balances with related parties
22 
23 
24 
25 
26 
27 
Report
Cell Formulas
RangeFormula
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
JKLMNOPQRSTUV
3GROUP COMPANY
420112012201320142011201220132014
5$mil$mil$mil$mil$mil$mil$mil$mil
6Non-Current Assets
7Property, plant and equipment761.9791.1857.4911.1344.9367.7384.1400.5
8Intangible assets423.6397.0380.6404.5126.9105.992.179.3
9Subsidiaries1,202.11,202.11,692.11,692.1
10Deferred tax assets2.82.5
11Balances with related parties (Assets-Long-Term)0.00.00.0600.0123.0106.0
12Total Non-Current Assets1,188.31,190.61,238.01,315.61,673.92,275.72,291.32,277.9
13
14Current Assets
15Inventories37.228.143.242.429.021.236.736.8
16Trade receivables152.0142.3123.5161.7139.9130.9108.5147.7
17Other receivables, deposits and prepayments149.2123.6154.4185.922.020.429.834.0
18Balances with related parties (Assets-Short-Term)17.212.024.017.4661.648.2111.3272.6
19Cash and bank balances179.2312.0266.9264.2154.3245.7207.5176.0
20Total Current Assets534.8618.0612.0671.61,006.8466.4493.8667.1
21
22Current Liabilities
23Trade payables and other payables and accurals702.9728.1753.7795.8353.4344.5335.3342.9
24Balances with related parties (Liabilities-Short-Term)41.956.576.599.2296.0321.5402.8385.6
25Bank Loans (Short-Term)75.0200.075.0200.0
26Provision for taxation83.594.671.497.955.751.435.035.5
27Total Current Liabilities903.3879.2901.61,192.9780.1717.4773.1964.0
28
29Non-Current Liabilities
30Bank Loans (Assets-Long-Term)587.5587.5
31Other payables34.137.528.923.334.137.528.923.3
32Borrowings687.5687.5487.5687.5687.5487.5
33Deferred income62.841.721.37.3
34Deferred tax liabilities112.8119.2128.0127.2-69.872.475.875.9
35Total Non-Current Liabilities797.2885.9865.7645.3-691.4797.4792.2586.7
36
37Net Assets22.643.582.7149.01,209.21,227.31,219.81,394.3
38
39Shareholders Equity
40Share capital262.8263.1271.4282.6262.8263.1271.4282.6
41Reserves-240.2-219.6-188.7-133.6946.4964.2948.41,111.7
42
43Total Equity22.643.582.7149.01,209.21,227.31,219.81,394.3
Master Data
 
You must log in or register to reply here.

Similar threads

A
  • Question Question
Lookup Material Name Material Unit/Day Consumed Duration and accumulate and compact abstract to the destination
Replies
2
Views
140
abuharvey
A
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
143
babyjwhale
B
J
  • Solved
LET/VSTACK Help
Replies
2
Views
210
Jedi Master
J
M
  • Question Question
Customer Ageing Different Formulas
Replies
3
Views
348
Alex Blakenburg
A
J
  • Question Question
copying cell value from columns that will change
Replies
4
Views
101
jbrown021286
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top