-2

I have an excel workbook titled "Invoices" that contains invoicing templates for approximately 35 employees. Cosmetically, all of the sheets are the same, except for employer, pay rate, name etc. Each employee has a different sheet within this workbook.

In addition, I get weekly "Master Time card reports" for approximately 120 employees that I must download as a separate excel workbook weekly. Each one of these employees has a separate worksheet. The data from the "Master Time card reports" must be transferred to the templates on the "Invoices" workbook. I have been doing this manually, and have been trying to figure out a way in Excel to dynamically reference the sheets in the "Master Time card reports" workbook from the "Invoices" workbook.

The issue is that the cell addresses on the "Master time card reports" change, depending on a number of factors. One week, the total hours for any particular employee may be in cell "T50", and then the next week they may be in cell "T43" (they never change columns). Also, the names of the sheets may change from "Sheet1" to "Sheet3", because new employees are added, or roll off.

I have done an extensive amount of research and can't seem to find a way to do this.

This is the formula that I am using so far:

=INDIRECT("'[Mastetimecardreports.xlsx]*'!B"&MATCH($D18,[Mastetimecardreports.xlsx]Sheet1:Sheet129!$B:$B,0)+1)

My goal is to enter the name of an employee on their respective worksheet in the "Invoices" workbook and have that name be found in the "Master time card reports" workbook and pull data from the sheet for that respective employee. I want to do this dynamically, as the cell address and sheets will always be changing.

teachmehowtodata
  • 1

1 Answers1

2

You may greatly benefit from a data architecture restructure. Whenever I read something like Each employee has a different sheet within this workbook, my alarm bells go off. Why would you do that? Why would you spread your data around multiple workbooks and inside those workbooks into multiple worksheets? This is the reason for your difficulty to aggregate the data. Use ONE workbook and ONE worksheet to store the data. If your data collection is in different files, Power Query can be used to create the ONE data file. Then you can easily create reports off that one data file.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • I would love to do that. But I get the weekly data from an outside source, and each employee must be on a different worksheet, because I must make PDFs of all the worksheets separately. – teachmehowtodata Apr 13 '19 at 19:23
  • Again, there is no need to be rude and condescending. I'm used to programming in R and JavaScript. I don't use Excel, and the task that I am completing is in Excel. You get real. – teachmehowtodata Apr 16 '19 at 20:42
  • I'm not sure why you keep mentioning my being "rude". If you don't want help, don't post in sites that offer help. I was trying to give you some pointers for proven good data architecture. With Excel's modern tools like Power Query you can shape a bad data source and turn it into a useful one. If saying that offends you, I'm truly sorry. – teylyn Apr 17 '19 at 07:35