Dynamic Offset Formula to populate a Data Validation list

Hi All,

I'm trying to use an OFFSET formula to populate a data validation list so that when a team manager name is selected in another cell (A1, also a named range of ipMgr) it lists the team members relevant to that manager.

I have got the following 'static' formula to work correctly:

=OFFSET('Team Structure'!$B$8,0,0,COUNTA('Team Structure'!B8:B42),1)

I'm trying to amend the formula to replace the $B$8 with another dynamic formula that provides the relevant cell reference based on the manager selected in cell A1/ipMgr. The formula I have for this part is:

=ADDRESS(8,MATCH(ipMgr,TeamMgrs,0)+1)

"TeamMgrs" is a range in the Team Structure sheet that lists the managers, with their team members directly underneath.

However, I'm having some trouble merging the two as the second formula just returns the cell reference and I need to combine this with the sheet name 'Team Structure'! I'm sure I'm doing something stupid here, but I've tried CONCATENATE and INDIRECT, but can't find anyway to get it to work as it does in the first formula.
Indirect example below, will only return the first team member from the list, not the full list, which I guess it due to INDIRECT returning the value rather than the address.

OFFSET(INDIRECT("'Team Structure'!"&(ADDRESS(8,MATCH(ipMgr,TeamMgrs,0)+1))),0,0,COUNTA(INDIRECT("'Team Structure'!"&(ADDRESS(8,MATCH(ipMgr,TeamMgrs,0)+1)))&":B42"),1)

I can't get CONCATENATE to work at all!

Any pointers on where I'm going wrong with this?

Thanks, TC