Combing P&L spreadsheets from Different Tabs

Status
Not open for further replies.
H

helenshas

New Member
Joined
Dec 4, 2019
Messages
42
Office Version
  1. 365
Platform
  1. Windows
I Have several of these tabs from different departments and need to combine them as a group tab, is there a lookup or something I can use to make this quicker,
Reveiw of Spreadsheets.xlsx
ABC
8
9Sales.Apr
10Sales0
11Accrued Sales - Current Month0
12Accrued Sales - Previous Month0
13WIP - Current Month0
14WIP - Previous Month 0
15
16Sales Total-
17
18Monthly Cost of Sales
19Cost Of Sales Accrued-
20MaterialsF-
21PPE EquipmentF-
22Client FeesF-
23Plant/Equipment HireV-
24CIS LabourV-
25Reah Group Labour-
26CIS MaterialsF-
27ConsultancyF-
28
29Cost of Sales Total-
30
31Salaries.
32SalariesV-
33Salaries OfficeF-
34ERS NIV-
35ERS NI - OfficeV-
36Pension ContributionsV-
37Pension Contributions - Office,F-
38Pension Contributions - OfficeF-
39Salaries Total-
40
41Expenses
42Vehicle Rental-
43TrackingF-
44Vehicle LeasingF-
45Vehicle RepairsF-
46Vehicle Fuel-
47Mileage AllowanceV-
48Vehicle InsuranceV-
49Parking CostsV-
50Parking FinesF-
51Other Vehicle CostsV-
52Vehicle Tax-
53FaresV-
54AccomodationV-
55MarketingV-
56AdvertisingV-
57UniformV-
58PostageV-
59Office StationaryV-
60IT Support-
61Internet-
62subscriptions-
63Accounting Services-
64Waste-
65Rent and Rates-
66Health Insurance-
67Public & Employers Liability-
68Professional Indemnity Insurance-
69Office Insurance-
70Directors & Officers Insurance-
71Telephones-
72Mobile Telephones-
73Mobile Telephones - Office-
74Legal & Professional-
75Human Resources-
76Electricity-
77Bank Charges-
78Service Charges-
79CHAPS Fee-
80Leumi Discount-
81Leumi 3 Day Discount-
82Iwoca Loan Interest-
83Training & Development-
84Staff Meetings-
85Entertainment-
86Charity Donation-
87Miscellaneous-
88Suspense Account-
89Entertainment
90
91
92
Grouped Summary
Cell Formulas
RangeFormula
A10A10=+NL!C65
A11:A14A11=+NL!C150
C10C10=+NL!S65+NL!S149+NL!S202+NL!S243+NL!S287+NL!S337+NL!S386+NL!S429+NL!S478+NL!S533+NL!S578+NL!S623+NL!S667+NL!S758+NL!S811+NL!S864+NL!S913+NL!S962+NL!S1006+NL!S1029
C11:C12C11=+NL!S150+NL!S203+NL!S244+NL!S288+NL!S338+NL!S387+NL!S430+NL!S479+NL!S534+NL!S579+NL!S624+NL!S668+NL!S759+NL!S812+NL!S865+NL!S914+NL!S963+NL!S994+NL!S1007
C13:C14C13=+NL!S152+NL!S205+NL!S246+NL!S290+NL!S340+NL!S389+NL!S432+NL!S481+NL!S536+NL!S581+NL!S626+NL!S670+NL!S761+NL!S814+NL!S867+NL!S916+NL!S965+NL!S1009+NL!S1030
C16C16=SUM(C10:C14)
A19A19=+NL!C154
A20:A27A20=+NL!C66
C19C19=+NL!S154+NL!S207+NL!S248+NL!S292+NL!S342+NL!S391+NL!S434+NL!S483+NL!S538+NL!S628+NL!S672+NL!S763+NL!S816+NL!S869+NL!S918+NL!S967
C20C20=+NL!S66+NL!S155+NL!S208+NL!S249+NL!S293+NL!S343+NL!S392+NL!S435+NL!S484+NL!S539+NL!S583+NL!S629+NL!S673+NL!S712+NL!S764+NL!S817+NL!S870+NL!S919+NL!S968+NL!S996+NL!S1011+NL!S1032
C21C21=+NL!S67+NL!S156+NL!S209+NL!S294+NL!S393+NL!S436+NL!S485+NL!S540+NL!S630+NL!S713+NL!S765+NL!S818+NL!S871+NL!S920
C22C22=+NL!S68+NL!S250+NL!S295+NL!S344+NL!S394+NL!S631+NL!S766+NL!S819
C23C23=+NL!S69+NL!S157+NL!S210+NL!S251+NL!S296+NL!S345+NL!S395+NL!S437+NL!S486+NL!S541+NL!S584+NL!S632+NL!S714+NL!S767+NL!S820+NL!S872+NL!S921+NL!S969
C24C24=+NL!S70+NL!S158+NL!S211+NL!S252+NL!S297+NL!S346+NL!S396+NL!S438+NL!S487+NL!S542+NL!S585+NL!S633+NL!S674+NL!S768+NL!S821+NL!S873+NL!S922+NL!S970+NL!S1012+NL!S1033
C25C25=+NL!S71+NL!S159+NL!S347+NL!S397+NL!S439+NL!S488+NL!S543+NL!S586+NL!S769+NL!S822+NL!S874+NL!S923+NL!S1034
C26C26=+NL!S72+NL!S160+NL!S212+NL!S253+NL!S298+NL!S348+NL!S398+NL!S440+NL!S489+NL!S544+NL!S587+NL!S634+NL!S770+NL!S823+NL!S875+NL!S924+NL!S971+NL!S1035+NL!S1036
C27C27=+NL!S73+NL!S824
C29C29=SUM(C19:C28)
A32:A36A32=+NL!C74
C32C32=+NL!S74+NL!S161+NL!S213+NL!S254+NL!S299+NL!S349+NL!S399+NL!S441+NL!S490+NL!S545+NL!S588+NL!S635+NL!S675+NL!S715+NL!S771+NL!S825+NL!S876+NL!S925+NL!S972+NL!S997
C33C33=+NL!S75+NL!S162+NL!S214+NL!S255+NL!S300+NL!S350+NL!S400+NL!S442+NL!S491+NL!S546+NL!S589+NL!S636+NL!S676+NL!S716+NL!S772+NL!S826+NL!S877+NL!S926+NL!S973+NL!S1014+NL!S1038
C34C34=+NL!S76+NL!S163+NL!S215+NL!S256+NL!S301+NL!S351+NL!S401+NL!S443+NL!S492+NL!S547+NL!S590+NL!S637+NL!S677+NL!S717+NL!S773+NL!S827+NL!S878+NL!S927+NL!S974+NL!S998
C35C35=+NL!S77+NL!S164+NL!S216+NL!S257+NL!S302+NL!S352+NL!S402+NL!S444+NL!S493+NL!S548+NL!S591+NL!S638+NL!S678+NL!S718+NL!S774+NL!S828+NL!S879+NL!S928+NL!S975+NL!S1016+NL!S1040+NL!S1015+NL!S1039
C36C36=+NL!S78+NL!S165+NL!S217+NL!S258+NL!S303+NL!S353+NL!S403+NL!S445+NL!S494+NL!S549+NL!S592+NL!S639+NL!S679+NL!S719+NL!S775+NL!S829+NL!S880+NL!S929+NL!S976+NL!S999
C37C37=+NL!S79+NL!S166+NL!S218+NL!S259+NL!S304+NL!S354+NL!S404+NL!S446+NL!S495+NL!S496+NL!S550+NL!S593+NL!S640+NL!S680+NL!S720+NL!S776+NL!S830+NL!S881+NL!S930+NL!S977+NL!S1018+NL!S1042
C38C38=+'Tab 1'!C38
C39C39=SUM(C32:C38)
C42C42=+NL!S80+NL!S551+NL!S641+NL!S681+NL!S931
C43C43=+NL!S81+NL!S167+NL!S219+NL!S260+NL!S305+NL!S355+NL!S405+NL!S447+NL!S552+NL!S594+NL!S642+NL!S682+NL!S721+NL!S777+NL!S831+NL!S882+NL!S932+NL!S978
C44C44=+NL!S82+NL!S168+NL!S1019
C45C45=+NL!S83+NL!S169+NL!S220+NL!S261+NL!S306+NL!S356+NL!S406+NL!S448+NL!S497+NL!S553+NL!S595+NL!S643+NL!S683+NL!S722+NL!S778+NL!S832+NL!S883+NL!S933+NL!S979+NL!S1043
C46C46=+NL!S84+NL!S170+NL!S221+NL!S262+NL!S307+NL!S357+NL!S407+NL!S449+NL!S498+NL!S554+NL!S596+NL!S644+NL!S684+NL!S723+NL!S779+NL!S833+NL!S884+NL!S934+NL!S980+NL!S1020
C47C47=+NL!S85+NL!S171+NL!S263+NL!S308+NL!S358+NL!S499+NL!S555+NL!S724+NL!S834+NL!S935
C48C48=+NL!S86+NL!S172+NL!S222+NL!S264+NL!S309+NL!S359+NL!S408+NL!S450+NL!S500+NL!S556+NL!S597+NL!S645+NL!S685+NL!S725+NL!S780+NL!S835+NL!S885+NL!S936+NL!S981+NL!S1021
C49C49=+NL!S88+NL!S173+NL!S223+NL!S265+NL!S310+NL!S360+NL!S409+NL!S451+NL!S501+NL!S557+NL!S598+NL!S646+NL!S686+NL!S726+NL!S781+NL!S836+NL!S937+NL!S982+NL!S1044+NL!S1045
C50C50=+NL!S1046+NL!S1022+NL!S938+NL!S886+NL!S837+NL!S782+NL!S687+NL!S647+NL!S599+NL!S558+NL!S502+NL!S452+NL!S410+NL!S361+NL!S311+NL!S266+NL!S224+NL!S174+NL!S89
C51C51=+NL!S90+NL!S175+NL!S225+NL!S267+NL!S312+NL!S362+NL!S411+NL!S453+NL!S503+NL!S559+NL!S600+NL!S648+NL!S688+NL!S727+NL!S783+NL!S838+NL!S887+NL!S939+NL!S983+NL!S1047
C52C52=+NL!S91+NL!S176+NL!S226+NL!S268+NL!S313+NL!S363+NL!S412+NL!S454+NL!S504+NL!S560+NL!S601+NL!S649+NL!S689+NL!S728+NL!S784+NL!S839+NL!S888+NL!S940
C53C53=+NL!S92+NL!S177+NL!S227+NL!S269+NL!S314+NL!S364+NL!S413+NL!S455+NL!S505+NL!S561+NL!S602+NL!S650+NL!S690+NL!S729+NL!S785+NL!S840+NL!S889+NL!S941+NL!S984+NL!S1048
C54C54=+NL!S93+NL!S178+NL!S365+NL!S691+NL!S730+NL!S786+NL!S942+NL!S1023+NL!S1049
C55,C63,C66:C71,C75,C77:C82,C85,C87C55=+NL!S94
C56C56=+NL!S95+NL!S731
C57C57=+NL!S96+NL!S179+NL!S315+NL!S366+NL!S414+NL!S456+NL!S506+NL!S562+NL!S603+NL!S651+NL!S692+NL!S787+NL!S841+NL!S890+NL!S943
C58:C59C58=+NL!S97+NL!S732
C60C60=+NL!S99+NL!S316+NL!S415+NL!S507+NL!S563+NL!S652+NL!S842
C61C61=+NL!S100+NL!S734
C62C62=+NL!S101+NL!S180+NL!S228+NL!S270+NL!S317+NL!S367+NL!S416+NL!S457+NL!S508+NL!S564+NL!S604+NL!S653+NL!S735+NL!S788+NL!S843+NL!S891+NL!S944+NL!S1050
C64C64=+NL!S103+NL!S458+NL!S736+NL!S945+NL!S1051
C65C65=+NL!S104+NL!S737
C72C72=+NL!S111+NL!S509
C73C73=+NL!S112+NL!S181+NL!S229+NL!S271+NL!S318+NL!S368+NL!S459+NL!S510+NL!S565+NL!S605+NL!S654+NL!S693+NL!S738+NL!S789+NL!S844+NL!S892+NL!S946+NL!S985
C74C74=+NL!S113+NL!S182+NL!S272+NL!S790
C76C76=+NL!S115+NL!S739+NL!S893
C83C83=+NL!S122+NL!S183+NL!S511+NL!S606
C84C84=+NL!S123+NL!S184+NL!S512+NL!S741+NL!S845+NL!S894+NL!S947
C86C86=+NL!S125+NL!S514
C88C88=+NL!S131
A42:A48A42=+NL!C80
A49:A87A49=+NL!C88
A88A88=+NL!C131
A89A89=+NL!C124
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
This appears to be a duplicate of this thread here: Combining Tabs

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,215,472
Messages
6,125,004
Members
449,203
Latest member
Daymo66

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