Cell B3: Rows repeater listing the Companies in my database
Cell C3: NL(Table,User Setup...TableName=,B3&"UserSetup",Company=,B3)
This is a great way of producing an identical table for every company you have.
My problem: I need to create a "unique lookup field" in each table which should take the form of CompanyUserID, so ABCJoeBloggs.
As the Table is created in the same "row" as the repeater, column B does not repeat the first company (as it would in a formula-driven Jet Report). As a workaround, I'm looking for the company name in column B but in the first non-blank cell above each returned entry in the table. I have worked out the formula in Excel I think I want to use: =LOOKUP(2,1/(INDIRECT("B2:"&ADDRESS(ROW(),2))<>""),INDIRECT("B2:"&ADDRESS(ROW(),2)))&[@[User ID]]
This formula DOES work.
One of my fields is a Formula: =@NP("Formula","LOOKUP(2,1/(INDIRECT(""B2:""&ADDRESS(ROW(),2))<>""""),INDIRECT(""B2:""&ADDRESS(ROW(),2)))&[@[User ID]]")
Now here's my issue:
Whenever I run the report, Jet returns this formula as #N/A! but if I click into any of those fields after the report has run and press Enter (without making any changes), the formula updates for that one table (not all of the tables based on the repeater in B3).
It feels, based on what I've seen before, that Jet is unable to run the formula because something isn't known. But each time I've seen it before, I've tried to link a table to another table which hasn't pulled yet, which doesn't apply here.
QUESTION: What must I do to get Jet to accept this formula?
QUESTION: Is there another method of producing my desired results?
Thanks for taking the time to look at this.