Sheet Named Range lookup/Indirect function syntax

I am struggling transposing the syntax of an indirect/sheet-name Named range resource-finding formula from a formula in an existing single resource sheet lookup.

The spreadsheet is a resource database in excel where I use a three letter code for a company and a five letter code for an employee (two letters plus the three letter company code) which I call up to use to create letters, orders and other forms etc

Each company has one line on the database and that line contains a lot of information about the company (about 30 columns) followed by all the staff details. Each staff member has about 8 columns (various details including phone numbers, email addys etc), starting with the code and name in the first two columns. I have about 30 job titles as headers in the person record section: so in all about 240 columns for people.

I want to split the resources sheet into 5 separate sheets: for customers, consultants, staff, subs and supplies and Other, to cut down the number of columns in the person section (as I have different job titles for the different resources) and because the full sheet is about 6000 lines long.

The formula I am trying to transpose (D26 in the attached) for the person uses ADDRESS, MATCH, COUNTIF and CHOOSE to create cell addresses to create a single line array for the column section of INDEX based on the company code, which then looks up the person code to return the column containing the person code. I then use OFFSET to return the name, phone, phone no etc for that person. Because the formula was getting unwieldy, I have created helper cells for the column section of the INDEX formula (D30/E30). D24 and D25 are the same formula but without the helper cells.

The existing single-sheet formulas are in D17 and D18 and E18.

Input is at D14 and D15.

I have tried to transpose the existing formula to use the Named Range (on a separate sheet “People”) containing the 5 resources tab names, but the main formula won’t work at all, and the helper cells are only working for the first “Customer List” tab: If you input details for the “Staff” tab (or any other) the helper cells return #N/a.
There may be a simpler formula to do what I am trying to do but I get tongue-tied with syntax and have struggled with this for hours so far!

If anyone can see where the syntax is wrong or knows a simpler way to get the results, then Thanks

Glen

Thanks for your reply!

I am trying to learn myself - and getting a bit tongue tied! Hence the report of how I had failed! I created the workbook to try and solve the problem and failures were steps along the way

I will go away and look at your formulas and be back shortly as I have a Zoom meeting at 4pm and need to get set up for that: but I am grateful for your response as this has been driving me mad!

I did wonder if the extra cells in the "People" section was a problem - but I am still learning about this stuff. I am at the stage where i am staring to understand how to create more advanced formulas, but the indirect and Name funcions are still eluding me!

Glen

Again, thanks so much for your response – you have solved the problem, Thank you! And far more eloquently than my attempts.

I see your point about the outputs require – in my mind, I put the previous formulas I was using in the green boxes to explained the output based on the fact was trying to convert a single page lookup to multi-page: but yes, I should have been clearer – I think I was just too close in.

One thing I have learnt today is that you can put formulas in the Name box! That is a revelation!

I was at the ripping hair out stage. I have learnt a lot since I joined this forum – but my attempts at doing things for myself are still very clunky! But with each failure I learn a little more….

So, I can at least now read your formulas! I don’t quite get parts, as I will explain below – and if you have the time, I would appreciate if you could give me a bit of explanation as to what different parts of the formula are doing.

But at least I understand the intent now! You and others here have helped me get to this stage. I do want to learn and it has been great to have people like yourself here having the patience to help my bungled attempts! When I first joined the forum, I would take the formulas given and repeat them without really understanding them, now I am trying to understand and have gained enough knowledge from the help I have been given to do so!

I think a big part of my problem was not understanding how to create the range using Indirect/Named Range: this part of the first formula – ie in the “Named” formula:
INDIRECT("'"&INDEX(People, MATCH(TRUE,COUNTIF( INDIRECT("'"&People&"'!E5:AU20"),input!$D$14)>0,0))&"'!H5:AU20"),

My version was INDIRECT("'"&People&"'!E5:AU20") – just the middle part of yours…

I presume the following part is looking to see if the company code (D14) exists in the array being created:

MATCH(TRUE,COUNTIF( INDIRECT("'"&People&"'!E5:AU20"),input!$D$14)>0,0)

But I don’t understand why we would be doing that is in the middle of creating the array ie in the middle of this formula:

INDIRECT("'"&INDEX(People, &"'!H5:AU20")

I also don’t understand why INDIRECT("'"&People&"'!E5:AU20") is split by Index, nor why it is split where it is split.

I have other spreadsheets where this solution would be useful – so I want to try and understand how this formula works if you have a little time to explain?

Once again, thanks so much for your help!

One more thing - the "input!" before the D14 - is that there just because it is being entered in the Name box?