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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You must log in or register to reply here.

Similar threads

F
  • Question
Input Automation
Replies
1
Views
289
mumps
mumps
W
  • Question
If formula without skipping lines
2 3
Replies
22
Views
1K
Fluff
Fluff
K
  • Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
141
keranali
K
K
  • Solved
Suggestions on how to re-write this formula for faster calculations
2
Replies
17
Views
1K
keef2
K
K
  • Solved
#REF! after VBA delete row
Replies
5
Views
753
keef2
K

Forum statistics

Threads
1,215,043
Messages
6,122,816
Members
449,095
Latest member
m_smith_solihull

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