Summary sheet of multiple sheets

Olavfinnerud

New Member
Joined
Jun 7, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
Hi,

I am working with making a template in excel for writing hours at my workplace. In sheet one named “Oppsumering” I want to have a summary from the other sheets I add. Each sheet represents one week and contains the hours from different tasks. The sheet named “Uke—” is the layout for where you write hours for one week. The column C6:C17 is where you write the hours and the “resource” used. I need help with getting this information over to the summary sheet. The resource I write in the C6 cell should appear in the blank merged cells B9:D9 in the first sheet which is the summary sheet. I also need the information in the cells C15,C16,C17 to be inserted into the summary sheet under “Timer”, “Kost” and “Sum kost” respectively.

I have a button called “Ny ressurs”, when I press this the column C6:C17 gets copied and inserted into D6:D17 with blank cells so I can write in a new “Resource”. The columns in the D and F columns gets pushed one to the right (This two columns should not be added to the summary sheet). Everytime I add a new resource and writes the hours used for this resource, this should be inserted into the summary sheet. The same resources is used by different people, in the row 7 cell I write the named of the person who has used the resource. If I add multiple columns of the same resource, for example two people have used “Prosjektleder” I just want one “Prosjektleder” on the summary sheet. And the “Timer”, “Kost” and “sum” should be added together on the summary sheet next to the corresponding resource.

If you could, you can set it up so when I press the “Ny ressurs” button the resource also gets added to the summary sheet. I have attached pictures of the sheets.
I have tried to solve this problem by selecting the cells with the information i want to appear on the summary sheet and transpose and paste it to the summary sheet. But this dident work with merged cells and i also couldent make it do it also for the new resources i add.

All help is really appreciated :)

The code i have for the button mentioned to add a new resource is, (I dont know if this is relevant for the problem) :

Sub NY_2()
'Add column and push columns to rigth
Range("C6:C17").Copy
Range("C6").Insert Shift:=xlToRight
Range("D8:D14").ClearContents
Application.CutCopyMode = False
End Sub
 

Attachments

  • Summary sheet.png
    Summary sheet.png
    28.8 KB · Views: 5
  • Week sheet, where i write the hours.png
    Week sheet, where i write the hours.png
    33.1 KB · Views: 5

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
626
Office Version
  1. 365
Platform
  1. Windows
Power query would handle what you've listed.
 

Forum statistics

Threads
1,181,771
Messages
5,931,937
Members
436,810
Latest member
cantresm

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
Top