Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2020, 01:42 AM
Nurul Nurul is offline How do i link a data from different sheet on a summary page automatically Windows 8 How do i link a data from different sheet on a summary page automatically Office 2010
Novice
How do i link a data from different sheet on a summary page automatically
 
Join Date: Jan 2020
Location: Singapore
Posts: 1
Nurul is on a distinguished road
Send a message via AIM to Nurul
Default How do i link a data from different sheet on a summary page automatically

Hi all,



I am trying to tabulate a report of my colleagues spending hours on completing a task on a daily basis.
With different accounts for different colleague on daily basis, how do i make it easy for the summary to capture the report without having to link it manually.

i tried index and match, but cant seem to have it auto.

thank you in advance
Attached Files
File Type: xlsx January 2020.xlsx (46.5 KB, 2 views)
Reply With Quote
  #2  
Old 01-14-2020, 07:55 AM
ArviLaanemets ArviLaanemets is offline How do i link a data from different sheet on a summary page automatically Windows 8 How do i link a data from different sheet on a summary page automatically Office 2016
Expert
 
Join Date: May 2017
Posts: 517
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

In added example, all work registration is made in single table. You can get worktable for every consultant in given month from added report. Another report displays monthly summary report. Rest of tables are various registries which are needed to get various Data Validation Lists to work.


You can hide some register tables. And you can hide some calculated columns (colored brown) in data entry table and in consultant monthly report.


In summary monthly report, I couldn't understand from where some data was got, and what you tried to do. So the report is not finished.
Attached Files
File Type: xlsx WorkbookExample.xlsx (63.6 KB, 2 views)
Reply With Quote
  #3  
Old Yesterday, 08:32 AM
p45cal p45cal is offline How do i link a data from different sheet on a summary page automatically Windows 10 How do i link a data from different sheet on a summary page automatically Office 2019
Expert
 
Join Date: Apr 2014
Posts: 342
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

One formula in your Summary sheet, cell C5:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(C$3,INDIRECT($A5 & "!$B$6:$AB$6"),0))
Copy down and across to column ZZ.
The names in column A of the Summary sheet must match exactly the names on the tab of each sheet.
Your formulae in cells AC5:AD8 differ slightly from the above by looking up the numbers in row 4 instead of the text in row 3, I've adhered to that,
AC5 formula:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(AC$4,INDIRECT($A5 & "!$B$7:$AB$7"),0))
but I suspect there's no reason they can't be the same as the formulae in columns C to ZZ.


Streamlined your formula in C11 to:
=SUMPRODUCT(C$5:C$8,$B$5:$B$8)
copied across.

Please double check the results are what you're expecting in the attached.
Attached Files
File Type: xlsx msofficeforums44223January 2020.xlsx (35.6 KB, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet mcronin Excel 1 05-09-2016 09:43 AM
How do i link a data from different sheet on a summary page automatically Automatically Organize data in a sheet differently (in order to create a dynamically updated graph) carlos_cs Excel 3 05-04-2016 08:44 AM
How to copy data automatically to particular sheet? nicholes Excel Programming 13 03-12-2014 01:05 AM
How do i link a data from different sheet on a summary page automatically Construct a summary sheet by summing up from one or more than one sheet. PRADEEPB270 Excel 1 11-04-2011 03:46 AM
How do i link a data from different sheet on a summary page automatically Link data from embedded excel sheet rwbarrett Word 1 05-27-2011 02:05 AM


All times are GMT -7. The time now is 04:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft