Valid data from other file (table) to P&L

P

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
890
Hi to everyone, i need your support so that to create vba code, to match the cells from P&L "B13" & "C14" with table (name file "OCCUPANCIES" in Desktop) and place them in col. "B" in row "OCCUPANCY" as my below extract. Note that, when i prepare the P&L for FEBRUARY 2017 should valid the data for respectively month and so on. In addition the row "OCCUPANCY" is change due to P&L data. Many thanks in advance




<colgroup><col style="mso-width-source:userset;mso-width-alt:6400;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="width:48pt" span="12" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 175"]PROJECT[/TD]
[TD="class: xl66, width: 255, colspan: 4"]JANUARY 2017[/TD]
[TD="class: xl66, width: 256, colspan: 4"]FEBRUARY 2017[/TD]
[TD="class: xl66, width: 256, colspan: 4"]MARCH 2017[/TD]
[TD="class: xl66, width: 256, colspan: 4"]APRIL 2017[/TD]

[TD="class: xl65"] [/TD]
[TD="class: xl69"]MONTH[/TD]
[TD="class: xl64"]MONTH %[/TD]
[TD="class: xl64"]YEARLY[/TD]
[TD="class: xl70"]YEARLY %[/TD]
[TD="class: xl69"]MONTH[/TD]
[TD="class: xl64"]MONTH %[/TD]
[TD="class: xl64"]YEARLY[/TD]
[TD="class: xl70"]YEARLY %[/TD]
[TD="class: xl69"]MONTH[/TD]
[TD="class: xl64"]MONTH %[/TD]
[TD="class: xl64"]YEARLY[/TD]
[TD="class: xl70"]YEARLY %[/TD]
[TD="class: xl69"]MONTH[/TD]
[TD="class: xl64"]MONTH %[/TD]
[TD="class: xl64"]YEARLY[/TD]
[TD="class: xl70"]YEARLY %[/TD]

[TD="class: xl65"]PROJECT 1[/TD]
[TD="class: xl71, align: right"]1598[/TD]
[TD="class: xl63, align: right"]65,75[/TD]
[TD="class: xl63, align: right"]8478[/TD]
[TD="class: xl72, align: right"]75,28[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 2[/TD]
[TD="class: xl71, align: right"]2025[/TD]
[TD="class: xl63, align: right"]87,35[/TD]
[TD="class: xl63, align: right"]11456[/TD]
[TD="class: xl72, align: right"]88,22[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 3[/TD]
[TD="class: xl71, align: right"]999[/TD]
[TD="class: xl63, align: right"]61,23[/TD]
[TD="class: xl63, align: right"]5125[/TD]
[TD="class: xl72, align: right"]45,28[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 4[/TD]
[TD="class: xl71, align: right"]3145[/TD]
[TD="class: xl63, align: right"]89,95[/TD]
[TD="class: xl63, align: right"]15243[/TD]
[TD="class: xl72, align: right"]78,41[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 5[/TD]
[TD="class: xl71, align: right"]4125[/TD]
[TD="class: xl63, align: right"]90,99[/TD]
[TD="class: xl63, align: right"]35678[/TD]
[TD="class: xl72, align: right"]100,12[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 6[/TD]
[TD="class: xl71, align: right"]2125[/TD]
[TD="class: xl63, align: right"]100,2[/TD]
[TD="class: xl63, align: right"]35478[/TD]
[TD="class: xl72, align: right"]2578[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 7[/TD]
[TD="class: xl71, align: right"]1458[/TD]
[TD="class: xl63, align: right"]125,12[/TD]
[TD="class: xl63, align: right"]45125[/TD]
[TD="class: xl72, align: right"]25,78[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 8[/TD]
[TD="class: xl71, align: right"]4569[/TD]
[TD="class: xl63, align: right"]15,45[/TD]
[TD="class: xl63, align: right"]36125[/TD]
[TD="class: xl72, align: right"]45,96[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

[TD="class: xl65"]PROJECT 9[/TD]
[TD="class: xl71, align: right"]6987[/TD]
[TD="class: xl63, align: right"]12,55[/TD]
[TD="class: xl63, align: right"]45874[/TD]
[TD="class: xl72, align: right"]12,45[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl72"] [/TD]

</tbody>





<colgroup><col style="mso-width-source:userset;mso-width-alt:6400;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> </colgroup><tbody>
[TD="class: xl65, width: 175"]PROJECT 6[/TD]
[TD="width: 68"][/TD]
[TD="width: 64"][/TD]
[TD="width: 61"][/TD]
[TD="width: 62"][/TD]

[TD="class: xl65"]PROFIT & LOSS[/TD]
[TD="class: xl65, colspan: 2"]JANUARY 2017[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"]MONTH[/TD]
[TD="class: xl68"]MONTH %[/TD]
[TD="class: xl68"]YEARLY[/TD]
[TD="class: xl68"]YEARLY %[/TD]

[TD="class: xl69"]ACCOUNTS[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl69"]INCOME[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Accommodation[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Food Income[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Beverage Income[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Other Income[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]TOTAL INCOME[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl69"]COST OF SALES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Food Cost [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Beverage Cost[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl69"]ADMINISTRATION EXPENSES [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Printing & Stationery[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Postages & Transportations[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]Rates & Licenses [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]TOTAL ADMINISTR.EXP.[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl69"]NET PROFIT / LOSS[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl70"]OCCUPANCY[/TD]
[TD="class: xl72, align: right"]2125[/TD]
[TD="class: xl71, align: right"]100,2[/TD]
[TD="class: xl71, align: right"]35478[/TD]
[TD="class: xl73, align: right"]2578[/TD]

</tbody>
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Question Question
pulling data from one chart to another
Replies
1
Views
810
jb00976
J
H
  • Question Question
Vlookup with multiple inputs?
Replies
4
Views
857
Aladin Akyurek
Aladin Akyurek
R
  • Question Question
Highlighting Specific Cells In Another Table
2
Replies
10
Views
1K
Yongle
Yongle
S
  • Question Question
Pull unique names refrencing date field
Replies
3
Views
697
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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