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]

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

No problem.

This might get you started:

B11: =IF(ISNUMBER(SEARCH($C$7,[Minor Faction Name])),MAX(B$10:B10)+1,0)

 

[XCaliber]

Ok

In the "ID" column I want to have a Unique ID.

to do this I show you 3 steps in the new file: https://drive.google.com/open?id=0B3fREpgAxamJNFRQY3JWa09LV28

In step 3 (Cell J12) I have added to the already existing formula the Max function.

Originally in the video I watched and am learning from used the following Max function: MAX($B$11:B11)+1

this formula works great in a Non Table format, but I have a table format so I believe I need table nomenclature to get this Max Function to work.

As shown in this video without Table format: * Timeline 5:48 to 6:35 minutes (scroll to 5:48 for quick view). https://www.youtube.com/watch?v=Jr34riKGveg

So what I need to know is ...

How to write the New Max function in table nomenclature.

I have tried the following and just can't get it to work yet:


MAX(Index([ID],1):[ID])+1
MAX(TBL_Minor_Factions[[Id]:[Id]]:TBL_Minor_Factions[Id])+1
MAX($B$11:TBL_Minor_Factions[[#Data],[ID]])+1
MAX(INDEX([Id],1):[@Id])+1

I need the first cell to be absolute as it was in the original $B$11 then extend to the end of the column so it can now find the (1) and then find the next effected cell and give it (2) then the next and give it (3) etc.... * see note above.

If you know how to rewrite the Max function found withing (Cell J12) this formula should work. It should give the following ID to the Names below:


1 Sekh Empire League
2 Sekh Universal & Co
3 Sekh Jet Vision Group
4 Sekh Liberals
5 Sekh Pirates

Rewriting the Max function within this formula found in (Cell J12) is what I need preferably in table nomenclature or whatever function you know that will achieve the same thing.

 

[StephenCrump]

You have circular references again:

Replace

J12: =IF(ISNUMBER(SEARCH($K$8,TBL_Minor_Factions3[[#Data],[Minor Faction Name]])),MAX($J$12:J12)+1,0)

with:

=IF(ISNUMBER(SEARCH($K$8,TBL_Minor_Factions3[[#Data],[Minor Faction Name]])),MAX($J$11:J11)+1,0)

 

[XCaliber]

I copied and pasted this line you gave:

=IF(ISNUMBER(SEARCH($K$8,TBL_Minor_Factions3[[#Data],[Minor Faction Name]])),MAX($J$11:J11)+1,0)

and it worked !!!

I just don't understand how it worked. Forgive me but, I thought that by starting with $J$11:J11 it would include the header. But it clearly did not. Wow !!

You just saved me a lot of Time. Thank you so much.

 

[XCaliber]

Lol I got so excited that I didn't even realize that it's not using table nomenclature. Is it possible to do so? If so, do you know how it should read?

 

[StephenCrump]

Lol I got so excited that I didn't even realize that it's not using table nomenclature. Is it possible to do so? If so, do you know how it should read?

Yes, on reflection it would be more robust to use structured references, so let me retract my comment in Post#2.

Try replacing:

=IF(ISNUMBER(SEARCH($K$8,TBL_Minor_Factions3[[#Data],[Minor Faction Name]])),MAX($J$11:J11)+1,0)

with:

=IF(ISNUMBER(SEARCH($K$8,TBL_Minor_Factions3[[#Data],[Minor Faction Name]])),MAX(TBL_Minor_Factions3[[#Headers],[Id]]:INDEX(TBL_Minor_Factions3[[#All],[Id]],ROW()-ROW(TBL_Minor_Factions3[[#Headers],[Id]])))+1,0)

 

[XCaliber]

Thanks !

 

[XCaliber]

This little bit I do not understand fully what it does. If possible, would you mind explaining it to me "ROW()-ROW". Great formula btw! It works..It really works !

 

[XCaliber]

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...



Here's the file link: https://drive.google.com/open?id=0B3fREpgAxamJV0k3Rlp6ZkxFUVk

YouTube Tutorial: https://www.youtube.com/watch?v=Jr34riKGveg&index=1&list=PLZGqBSnWx7nWRfRl0qtUd_CmfL2kYR5Ks Quick View time line 10:39 to 11:36.

He later updated his formula to lockdown (Cell F2) Quick View Time line 12:44 To 13:08.



In My File (Cell L8) the following formula Is supposed to Capture the list (Cells L12:L50) formed from the previous formula in (Cells J12:J50).

Formula: =OFFSET('Minor Factions'!$D$11,,,COUNTIF('Minor Factions'!$D$11:D11,"?*"))


And from your last lesson, I figured the following would work using Table Nomenclature:

=OFFSET(TBL_Minor_Factions3[[#Headers],[Srch Results]],,,COUNTIF(TBL_Minor_Factions3[[#Headers],[Srch Results]]:INDEX(TBL_Minor_Factions3[[#All],[Srch Results]],ROW()-ROW(TBL_Minor_Factions3[[#Headers],[Srch Results]])),"?*"))



But neither work.

These results will eventually be in the Searchable drop down List (Cell K8).

As you may have guessed, I have difficulty with absolutes and relatives for some reason. I know this issue is simular to the previous you helped me with.

 

[StephenCrump]

This little bit I do not understand fully what it does. If possible, would you mind explaining it to me "ROW()-ROW". Great formula btw! It works..It really works !

In essence, you have a column of TRUE's generating results 1 .. 2 .. 3 .. 4, as below.

B4: =ISNUMBER(SEARCH(B$1,[@Name]))
C4: =ROW() returns the Excel row
D4: =ROW()-ROW(MyTable[[#Headers],[TableRow]]) returns the Table row
E4: =IF([@[Match?]],MAX(E$3:E3)+1,0) is the non-structured reference
F4: =IF([@[Match?]],MAX(MyTable[[#Headers],[Result2]]:F3)+1,0) structures the anchor reference to the header row
G4: =IF([@[Match?]],MAX(MyTable[[#Headers],[Result2]]:INDEX(MyTable[[#All],[Result3]],[@TableRow]))+1,0) uses INDEX to structure the variable row reference.

--> H4: =IF([@[Match?]],MAX(MyTable[[#Headers],[Result2]]:INDEX(MyTable[[#All],[Result3]],ROW()-ROW(MyTable[[#Headers],[TableRow]])))+1,0)

Excel 2010

	A	B	C	D	E	F	G	H
Name	Match?	ExcelRow	TableRow	Result1	Result2	Result3	Result4
XYZ
ABC
XYZ etc
Another XYZ
DEF
XYZ plus

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Find:[/TD]
[TD="bgcolor: #FFFF00, align: center"]XYZ[/TD]
[TD="align: right"][/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="align: right"]FALSE[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

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

[TD="align: right"]FALSE[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

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

[TD="align: right"]TRUE[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

</tbody>

1​