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