• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Formula links to data in other files|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Formula links to data in other files|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Formula links to data in other file…
sp_PrintTopic sp_TopicIcon
Formula links to data in other files
Avatar
Declan Slemon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2014
sp_UserOfflineSmall Offline
1
August 30, 2017 - 11:00 pm
sp_Permalink sp_Print

Hi Mynda

I developed an excel based time reporting system for our company.

Each employee saves a timesheet template (T_EMPLATE.xlsx) with their name (e.g. J_Brown.xlsx, J_Jones.xlsx).

The same folder also contains a summary file (Summary.xlsx) that consolidates the data from individual timesheet files. This file contains a sheet per week that pulls in data for 60 or so employees against 80 or so project codes. Each column is header by employee names from a list on the Setup sheet in the same file, Gaps are left available in this list for new employees as 'T_EMPLATE'. Similarly I have left columns available in each sheet for new employees linked to T_EMPLATE.xlsx.

When a new employee joins, I currently have to manually update this summary file, swapping out T_EMPLATE.xlsx with the new employee file reference in an available column of all week sheets (grouped).

I want to know how I can automate this, so that by simply adding a new employee's name or filename to a list on a Set-up sheet within Summary.xlsx, that individual's file would be picked up by the Summary file look to a different file if the name is changed.

Similarly, moving people between departments is a manual task. Department information is available in each individual's timesheet file, but I am unable to create the formula to reference a different file is the column header name changes.

I expect there is an easy solution to this, but I've been looking for a few years with no success. If I could solve this, our system would be full automated with minimal effort to add or change employees.  

Regards

Declan Slemon

Aerogen Ltd

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
August 31, 2017 - 11:31 am
sp_Permalink sp_Print

Hi Declan,

It's difficult to give you formula advice without seeing your file, but the process you describe is something Power Query (available in Excel 2010 onward) is designed to automate.

With Power Query you can create your summary file from the individual workbooks, as opposed to having to maintain the summary file and all of its formulas. You'd put all of your time sheet workbooks into a folder and Power Query would collate the data from those files into a table in a new file. If you added a workbook to the 'time sheet folder' you'd simply click the 'Refresh all' button and Power Query would get the new data.

Instead of having the employee's department name in their file, you'd have a lookup table that maps each employee to a department, which you can then merge with your Power Query data. If an employee changes department, you'd change the lookup table and this would feed through to the Power Query data.

This post shows how to use Power Query to consolidate data on multiple worksheets in the same file, not quite the same as your scenario with the data in different files, but it'll give you an insight into Power Query and might help you visualise how it can help.

I don't recommend you attempt this without learning Power Query first. While Power Query is fairly intuitive to use and quick to learn, as with anything, you have to set things up right otherwise you'll run into problems later on. On my Power Query course page here there is some more information on Power Query and how you install it etc.

I hope that points you in the right direction. Please let me know if you have any questions.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Declan Slemon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2014
sp_UserOfflineSmall Offline
3
September 12, 2017 - 2:41 am
sp_Permalink sp_Print

Hi Mynda

Thank you for your response and for directing me to Power Query. I will look further into that functionality for future projects. However, in this incidence, the Summary file has lots of other information such as quarterly resource forecasts and reports by department & project that are used by our company. I'd prefer not to rebuild the file at this time as it is a source link for many reports.

My query really only relates to the following

I have simple formulae of this nature in columns, starting from row 2;

='C:Documents[J_Brown.xlsx]Summary'!P2      (Formula A)

Where J_Brown.xlsx is an individual employee's timesheet file.

The result of these formula is the number of hours assigned to a project for an individual in a specific time period.

 

The header of the column, row 1, is populated with the individual's name from a lookup table in a setup sheet in the same file.

=IF(Setup!G$2="","",Setup!G$2)       (Formula B)

The format of the names in the lookup table is actually the full filename.

J_Brown.xlsx    (Result B)

I am interested to learn whether I can replace the "J_Brown.xlsx" text in Formula A above with an expression that would use the value in the row 1 column header cell  to find the relevant employee's file rather than having to specify it in Formula A.

I could send an example file if that would help, but was unable to attach in this Forum post.

Any assistance on this specific query would be most appreciated.

Regards

Declan Slemon

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 12, 2017 - 12:20 pm
sp_Permalink sp_Print

Hi Declan,

You would have to use the INDIRECT function e.g. =INDIRECT("'C:Documents"&FormulaB&"Summary'!P2") , but as INDIRECT is a volatile function, excessive use of it will make your workbook slow.

Plus, the file you're referencing must be open, or INDIRECT will display #REF! errors.

Probably not ideal.

Just to clarify, you can add Power Query to your existing file. You don't have to start a new file from scratch as it's not a special file type or anything like that.

Mynda

Avatar
Declan Slemon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2014
sp_UserOfflineSmall Offline
5
September 13, 2017 - 2:24 am
sp_Permalink sp_Print

Thanks Mynda

I'll try the INDIRECT function before looking further into Power Querys.

Regards

Declan

Avatar
Zubair Muhammad

New Member
Members
Level 0
Forum Posts: 1
Member Since:
September 20, 2017
sp_UserOfflineSmall Offline
6
September 20, 2017 - 10:06 pm
sp_Permalink sp_Print

Hi Declan,

Try the PULL_A_CELL function. It is a small user defined function included inside an Add-In I created

http://www.excelnaccess.com/co.....workbooks/

This function takes the following syntax and will work perfectly in your case (without the need to have the workbook open as required by INDIRECT function)

=PULL_a_CELL(WBpath,Sheetname,celladdress)

=PULL_a_CELL("C:\Documents\" & FormulaB ,"Summary",P2)

Regards,

Zubair

Avatar
Declan Slemon

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
October 17, 2014
sp_UserOfflineSmall Offline
7
January 23, 2018 - 9:50 pm
sp_Permalink sp_Print

Thank you Zubair

I apologise for missing your suggestion until now.

Will this functionality continue to work if additional workbooks are added to the folder as new employees are added?

Do all users of the Summary file require your add-in?

Regards

 

Declan

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sonja Mason, Alexandra Radu
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.