VLOOKUP or INDIRECT or ? ... I need some help

[SPI OCU]

Hello Everyone

I am currently updating an old template that my dept uses, and trying to make it more efficient. This template pulls data from our employee's attendance files and lists them in a single spreadsheet. One of the many problems I am seeing with the workbook, is the time that it takes to update the formulas if we move agents to a new team, or if we have a new hire class come in. There are actually 10 columns that are built as Im describing below, so when there are changes, it can be a real headache. I've listed a crude table below with the formulas that are listed, and then I will pose my question.

Column A____________Column B___Column C

<TABLE dir=ltr border=1 cellSpacing=2 borderColor=#000000 cellPadding=2 width=294><TBODY><TR><TD bgColor=#c0c0c0 height=17 width="51%">

Agent Name​

​

​

​

​

</TD><TD bgColor=#c0c0c0 height=17 width="29%">

Supervisor​

​

​

​

​

</TD><TD bgColor=#c0c0c0 height=17 width="20%">

OFLA​

​

​

​

​

</TD></TR><TR><TD height=16 width="51%">

Doe, John​

​

​

​

​

</TD><TD height=16 width="29%">

Supervisor A​

​

​

​

​

</TD><TD bgColor=#ffffff height=16 width="20%">

98​

​

​

​

​

</TD></TR><TR><TD height=16 width="51%">

Smith, John​

​

​

​

​

</TD><TD height=16 width="29%">

Supervisor B​

​

​

​

​

</TD><TD bgColor=#ffffff height=16 width="20%">

40​

​

​

​

​

</TD></TR><TR><TD height=17 width="51%">

etc.​

​

​

​

​

</TD><TD height=17 width="29%">

etc.​

​

​

​

​

</TD><TD height=17 width="20%">

etc​

​

​

​

​

</TD></TR></TBODY></TABLE>​

In Column C I have the following formula:​

='T:\Permanent Attendance Files\Supervisor A Team\[Doe, John.xls]OFLA'!$F$4​

I have almost 400 employees that are spread across 20 different Supervisors. My question is there a simple way to build this as a template so that I can have Column C pull that number from whatever Supervisor folder that agent in column A is assigned to?​

I know this formula listed below won't work, but portrays the general idea I'm trying to accomplish:​

='T:\Permanent Attendance Files\(Cell B2)'s Team\[(Cell A2)'s attendance file.xls]OFLA'!$F$4​

 

[Smitty]

Welcome to the Board!

You could try INDIRECT.EXT, which is part of the MoreFunc add-in and can pull data from closed workbooks. It's still likely to carry a performance hit though.

It sounds like this would be better off as a database though.

 

[SPI OCU]

Thanks for the pointer Smitty... Doe anyone know if it is possible to have a formula in D1 built that will read the bold piece of C1 and then continue with its own tab reference unique to cell D1?

So in D1...

=(Cell C1)'s text from: "='T:\Permanent Attendance Files\Supervisor A Team\[Doe, John.xls]"

and have D1 be referencing a tab in the target file above for absences? (ie Absences'!$G$10 instead of OFLA'!$F$4 as built in C1's formula)

This would be a quicker way for me to address my original inquiry since the INDIRECT seems like it may not work or may cause problemsa and would also seem to be better to only have to update one column when we do team changes or new hiring and have the other 9 columns in my spreadsheet contain the formula-concept I described.

Any thoughts?

 

[Smitty]

Wouldn't INDIRECT still be what you're looking for in this case?