+ Reply to Thread
Results 1 to 10 of 10

Sheet Named Range lookup/Indirect function syntax

  1. #1
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,603

    Re: Sheet Named Range lookup/Indirect function syntax

    Gosh! I wish you had told us what you wanted to do... rather than describing how your efforts failed!!

    I think you want to look up a company name on one of n sheets and return some individual details.

    1. Change your NR "People" to cover the exact range, not the exact range plus a few extra.

    2. Set up a 2nd NR (CoRow) using this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3. Use this formula in D17 copied across:
    =IFERROR(INDEX(CoRow,,MATCH($D$15,CoRow,0)+COLUMNS($D17:D17)),"")
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    Re: Sheet Named Range lookup/Indirect function syntax

    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!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,603

    Re: Sheet Named Range lookup/Indirect function syntax

    Hahaha. I couldn't follow your formulae at all, so I made a guess at your desired end result, deleted them all and started again.

    The joys of Zoom. I dislike the inability to interact/read body language, but in the times of the plague....

  5. #5
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    Re: Sheet Named Range lookup/Indirect function syntax

    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!

  6. #6
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    Re: Sheet Named Range lookup/Indirect function syntax

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

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,603

    Re: Sheet Named Range lookup/Indirect function syntax

    Oh dear!! I'll try, but the formula leaves me a bit distressed, too!!

    1. You (almost) never need to return the address of anything to use it in a subsequent formula.

    2. the formula

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

    Red: the COUNTIF looks at all the sheets defined by "People" and returns a 1 if it sees a match for D14, or a 0 if it doesn't. This defines the sheet where the result exists.

    Orange. Now it knows which sheet to look at, it looks at the range H5:AU20

    Green and looks for a match for D14

    Purple in column E of the sheet (repeating a bit to make sure it goes again to look at the correct sheet.

    Does that help???

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,603

    Re: Sheet Named Range lookup/Indirect function syntax

    Quote Originally Posted by QSGuy View Post
    One more thing - the "input!" before the D14 - is that there just because it is being entered in the Name box?

    Mmm. Not sure, TbH. Delete it. What happens? Safer to leave it!! I'm away now for BBQ and beer in the gathering darkness and gloom of a typical Irish summer's evening.

  9. #9
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    Re: Sheet Named Range lookup/Indirect function syntax

    Glen

    I am glad it is not just me who has to get his head around this stuff - thank for your patience in explaining!

    And Thanks again for your response!

    Edited:

    I did write a couple of posts last night, but having sat with the formula again this morning, I now "get it"

    The colours broke it down in a way I could understand and from that, and with a bit of help from the formula guide that comes up as you start to write formulas, I was able to see which part of the formula related to which function.

    When you get multiple nested formulas I have struggled to understand which of the individual functions a particular section relates to - but I took your coloured sections and put them into a Word doc and then split it all down using tabs to indent as I started a new INIDIRECT, INDEX or MATCH function, and from that could see a visual of which part of the formula related to which particular iINDIRECT, INDEX OR MATCH function - as all the particular parts of each one where on a different tab-line down the page. Then it was easy to get my head around the formula. Needless to say, I have saved that Word doc!

    If I am to write my own formulas better in future, I need to start to understand how they work, rather than just copy them - hence my questions here - as this is a query I come across often, I wanted to really get my head around this answer.

    I even now get the INDIRECT("'"&INDEX part at the beginning of the formula!

    So thanks for all your help!
    Last edited by QSGuy; 07-14-2021 at 07:50 AM.

  10. #10
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    95

    Re: Sheet Named Range lookup/Indirect function syntax

    Glen

    the Input! was my bad - that is the name of the sheet that D14 is in - so as we were in the NAME box, not in the Input! sheet, obviously Input! has to be in there! Doh!
    Last edited by QSGuy; 07-14-2021 at 07:54 AM. Reason: resolved solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Indirect Function won't accept the Named Range
    By Plastik mac in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-07-2020, 11:23 PM
  2. Indirect function with Named Range for use as a PT Data Source
    By andikeep2580 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-20-2015, 12:07 PM
  3. Replies: 9
    Last Post: 04-01-2015, 04:23 PM
  4. INDIRECT not referencing named range which exists on another sheet
    By rikkyshh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2014, 06:55 AM
  5. [SOLVED] named range via indirect. How to reference a specific sheet
    By Decar in forum Excel General
    Replies: 2
    Last Post: 11-28-2013, 03:05 PM
  6. Named range lookup using Index/Indirect/Match Function calls in Macro
    By sgopan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2013, 07:51 PM
  7. Using Indirect function to return a named range.
    By adrianodl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2012, 09:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1