0

Useing Arrays in the Table Function

Jet Reports Historic Posts

July 11, 2014 12:10

None

Follow

Hi All

I am building a large number of table:

In my "Options" page I have a matrix of values as:

1. Table ID
2. Table Name
3. Named Rage for the table field list

each with 20 columns, one for each table

I would like to replicate this across a number of worksheets, one for each table. the problem is:

1. I can replicate the Table ID
2. I can replicate the Table Name
3. I can not insert the field list into the argument

How can I insert the field list into the replicated table function?

Many thanks

Malcolm

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts August 05, 2014 13:58 Official comment

  Guys thanks for your suggestions, however Jae has come up with a solution:
  
  we can use the INDIRECT function to turn a cell reference back into an Array:
  
  =NL("Table",D8,INDIRECT(D9)) where D8 is the table ID and D9 is the text representing the Named Range (array) of field names in the table in question
  
  I have attached an example but you may have to adjust the tables and the Fields for your data source

  • Table Arrays for Data Extraction.xlsx
• 0
  
  Jet Reports Historic Posts July 14, 2014 11:54

  hi,
  please send an example of your Report, to see, what you want to do…
  
  thanks
  
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts July 14, 2014 23:31

  Hi Malcolm,
  
  While I would normally discourage the use of VLOOKUP, I think you could use it to achieve the sort of thing you're trying to do. I am attaching a sample workbook which I believe does what you want. Does this help?
  
  Regards,
  Hughes

  • Dynamic Tables by Sheet.xlsx
• 0
  
  Jet Reports Historic Posts July 24, 2014 10:06

  Hi All
  
  In the attached file I have built up a list of tables and the fields that I need to extract
  
  I am trying to find a way of replicating the NL Table across Sheets for all tables in the list. replicating the Table Numbers is easy the snag is linking each table to the list of fields. I can not just dump all fields as I am trying to match this into the import routine
  
  Thanks
  
  Malcolm

  • Data Migration Table Builder.xlsx
• 0
  
  Jet Reports Historic Posts July 24, 2014 13:42

  Hello mjohnson,
  
  first of all: i am not really familiar to the NL(table)-function……
  
  but, i would agree to Hughes, to Change rows to columns, to make it easier….
  like this:
  table1, field1,field2,field3
  table2, field1
  table3, field1, field2, field3,field4
  
  i think, this would be easier for your vlookup…
  but, as i said, i'm not really familiar to the NL(table)-function…… ;)
  
  regards
  jetsetter