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]

I can't see why you wouldn't use the exact same formula in a table?

Can you please provide an example to illustrate what you're trying to achieve?

 

[XCaliber]

Of course..

My ultimate goal is to create a searchable drop down List. I found the following video that meets my needs https://www.youtube.com/watch?v=Jr34riKGveg. It only requires an additional 2 columns with what I thought to be easy formulas. No vba necessary.

Using the formula's found in the video, I do not see the unique ID number ("ID" Column in my Table) beginning with 1.. then 2... then 3.. next to the effected cells. Instead... I see 100.. 101..102.. as the lowest. The Max function should find 1 then add 1 to the next occurrence hence.. I should see 1.. 2.. 3.. for the effected cells. Not only this, but after saving the file. The 100..101..102 becomes 200..201..202..

I should mention that I am using excel 2016 now and was using 2010 when I first created this Table.

Anyway..Here's the Formula which includes the use of the Max function: =IF(ISNUMBER(SEARCH($C$7,TBL_Minor_Factions[[#All],[Minor Faction Name]])),MAX($B$11:B11)+1,0)

Here are my alternate formulas (Using table Nomenclature) that I had hoped would give me the result mentioned above, but ended up giving the exact same result:


=IF(ISNUMBER(SEARCH($C$7,TBL_Minor_Factions[[#Data],[Minor Faction Name]])),MAX($B$11:TBL_Minor_Factions[[#Data],[ID]])+1,0)
=IF(ISNUMBER(SEARCH($C$7,[Minor Faction Name])),MAX(TBL_Minor_Factions[[Id]:[Id]]:TBL_Minor_Factions[Id])+1,0)

I hope this helps with the troubleshooting.

 

[StephenCrump]

Can you please post your actual table, and describe the results you're trying to get? Or post a link to your workbook?

The alternative is not particularly appealing, i.e. watching a 14 minute video and guessing what you may have done based on that.

 

[XCaliber]

Ok here's a link to the worksheet of which I spoke.

https://drive.google.com/open?id=0B3fREpgAxamJV0NxcnRCOFk2NlE

 

[XCaliber]

I wish I know how to edit a reply. Anyway...There's the file and in it you will see the formula in any of the cells within the column "ID". If you scroll down to row 576 you will see the 3 Names that are effected by my current search and you will also see that they are not numbered 1... 2... 3... as they should be.

 

[StephenCrump]

Thanks.

Your formula in B11 is: =IF(ISNUMBER(SEARCH($C$7,TBL_Minor_Factions[[#All],[Minor Faction Name]])),MAX(INDEX([Id],1):[@Id])+1,0)

Two immediate problems:

1. The ID column is referencing itself, so you have circular references.

2. Your ISNUMBER(SEARCH(..)) will always return FALSE unless you search on "Minor Faction Name" in $C$7,
because TBL_Minor_Factions[[#All],[Minor Faction Name]] consists of:
{"Minor Faction Name";"HIP 79067 Inc";"Sekh Empire League";......}

I am guessing that you need MATCH() rather than SEARCH(), but I am still not clear on what you are trying to achieve here.

What results would you like to see in B11, B12, B13 ... and why?

Cut down version of your table:

Excel 2010

	A	B	C	D	E
Mbutsi
HIP 79067 Inc
Sekh Empire League
Sekh Universal & Co
Sekh Jet Vision Group
Sekh Liberals
Sekh Pirates

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]Id[/TD]
[TD="bgcolor: #000000, align: center"]Minor Faction Name[/TD]
[TD="bgcolor: #000000, align: center"]Srch Results[/TD]
[TD="bgcolor: #000000, align: center"]Log?[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"]Id[/TD]
[TD="bgcolor: #000000, align: center"]Minor Faction Name[/TD]
[TD="bgcolor: #000000, align: center"]Srch Results[/TD]
[TD="bgcolor: #000000, align: center"]Log?[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #44546A, align: center"]Top[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Yes[/TD]

</tbody>

Minor Factions​

 

[StephenCrump]

Ahh!

I hadn't scrolled down that far, but now I get it. And it explains why you're using SEARCH() rather than MATCH().

I'll post another cut-down version of your spreadsheet soon, just to make sure we're on the right track.

 

[StephenCrump]

So for the scenario illustrated, you need the ID column to populate with the numbers 1, 2 and 3 as shown?

Is it that simple?

(Perhaps I should have watched the video, but I confess to having no patience to watch YouTube Excel clips).

Excel 2010

	A	B	C	D
Mbutsi
HIP 79067 Inc
Sekh Empire League
Mbutsi Company
Meelov Commodities
Regulatory State Of Mbutsi
Values Party of Mbutsi
Meelov Commodities

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #000000, align: center"]Id[/TD]
[TD="bgcolor: #000000, align: center"]Minor Faction Name[/TD]
[TD="bgcolor: #000000, align: center"]Srch Results[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #000000, align: center"][/TD]
[TD="bgcolor: #000000, align: center"]Id[/TD]
[TD="bgcolor: #000000, align: center"]Minor Faction Name[/TD]
[TD="bgcolor: #000000, align: center"]Srch Results[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #44546A, align: center"]Top[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Minor Factions​

 

[XCaliber]

Ok give me some time to make this easier on both of us. My apologies for not being better prepared.