How to use Table Nomenclature with the following Max function formula

[XCaliber]

Hi,

I need to use this formula, but use it within a Table that grows. In other words the array will become larger. I hope you can see what I am trying to do with this formula and I hope someone can show me how to recreate it for use in a table in stead of a Data set. Is there a way? If so, what is the Nomenclature to make this work?

MAX($B$11:B11)+1

 

[StephenCrump]

If Possible, could you help me complete the last part of the Tutorial I found by converting it to Table Nomenclature ? I am still struggling to comprehend...

No guarantees, but I'll try to take a look tomorrow.

 

[XCaliber]

No problem. Whenever you can will be fine with me.

Thank you.

 

[StephenCrump]

Here's your file: https://app.box.com/s/0xceydvp0byavvbka49kg47r5ogmnxj1

I have made two quick changes:

1. Using Name Manager, I created a name called SearchResults, defined as:

=OFFSET(TBL_Minor_Factions3[[#Headers],[Srch Results]],1,,COUNTIF(TBL_Minor_Factions3[Srch Results],"?**"))

2. I applied Data Validation to your cell C8, and allowed a List with Source: =SearchResults

Et voila! I think it does what you want it to do?

 

[XCaliber]

It is D8 that requires this formula.

After pasting formula into D8, The same formula is pasted into a Name call it "Search_Names" to keep things simular. Then the Validation list in C8 refers to this same name "Search_Names". Hope this clarifies.

I pasted the formula into D8 and it only returned a Value error.

=OFFSET(TBL_Minor_Factions3[[#Headers],[Srch Results]],1,,COUNTIF(TBL_Minor_Factions3[Srch Results],"?**"))

I should not need a 1 for Row it is the height only that is required. Seeing how its the Column (Srch Results) that this formula refers to.

However, I plugged this formula in (removed Row 1) and still got a Value error.
=OFFSET(TBL_Minor_Factions3[[#Headers],[Srch Results]],,,COUNTIF(TBL_Minor_Factions3[Srch Results],"?**"))

So it's not working yet for me.

With the information i provide here, would you mind taking another look at it?

 

[XCaliber]

The File link in post #19 is still Valid if you need it.

 

[StephenCrump]

After pasting formula into D8, The same formula is pasted into a Name call it "Search_Names" to keep things simular. Then the Validation list in C8 refers to this same name "Search_Names".

No, the formula in SearchNames is not pasted into a cell.

The formula returns a column of successful search results, which is the List (SearchNames) used in the Data Validation.

If you do put the formula in a cell, you can highlight the formula and evaluate with F9, and you'll see the full column of values. However, the value displayed in the cell will depend on the row of the cell relative to the Table, and will be #VALUE if outside the row range of the non-blank SrchResults.

The OFFSET( ...,1,,,...) is needed, because my formula offsets 1 row from the HeaderRow.

But alternatively, instead of:

=OFFSET(TBL_Minor_Factions3[[#Headers],[Srch Results]],1,,COUNTIF(TBL_Minor_Factions3[Srch Results],"?**"))

I could also have used:

=OFFSET(INDEX(TBL_Minor_Factions3[Srch Results],1),,,COUNTIF(TBL_Minor_Factions3[Srch Results],"?**"))

I am not clear why you want separate cells C8 and D8? I thought the intention was that you could type, say "Inc" in my C8, and click on the dropdown to get the two possibilities:

HIP 79067 Inc
Yarrite Incorporated

 

[XCaliber]

Ok the #Value in D8 threw me. "the value displayed in the cell will depend on the row of the cell relative to the Table, and will be #VALUE if outside the row range of the non-blank SrchResults."

You are correct. The formula works great!

Thank you so much!

Search drop down validation lists are so helpful For lengthy tables.

 

[XCaliber]

To answer your question: "I am not clear why you want separate cells C8 and D8?"

I was merely Using the you tube tutorial as my basis, because it worked for him. You saw right through it all and found an even better solution which works just as well without as many steps for creation (Now that's what I call Efficiency).

Many Thanks for your help with my Searchable Drop down Validation list for lengthy "Tables" using Table Nomenclature.

 

[XCaliber]

Ps. I really like your response in Post #20. Very descriptive and informational of what is actually happening within the formula.

 

[XCaliber]

Ok So now that it's working, I realized that the ID Column needs to be the 2nd column instead of the first Due to other Vlookup functions created long before this formula. Wow, does that ever throw in a monkey wrench.

Now I can't use Vlookup, but I could use Index and Match functions together as a replacement for Vlookup as I understand. I just don’t know how.

So Here's the formula that is working with the ID Column as the first column:

=IFERROR(VLOOKUP(ROWS(INDEX([Srch Results],1):[@[Srch Results]]),TBL_Minor_Factions[[#Data],[Id]:[Minor Faction Name]],2,0),"")

Now how to remove Vlookup function and replace it with Index and Match functions so the ID Column no longer has to be in the 1^st column?

Any help would greatly be appreciated.