0

Limit creation of new columns

Jet Reports Historic Posts

March 11, 2008 15:16

None

Follow

Universal connector 7.06 build 1759

$H$3 is empty Cell


Options!D19:D70 user can define columns they want on report as headers ( Note that are adjoining columns on options page contain criteria)

Code in question

=NL("Columns",IF(Options!$D19<>$H$3,Options!$D$19:$D$70,{" "}),Options!$D19,"DataSource=","Avantitest")


My problem is that I only want columns created where there is an entry on the options page Instead I get unnecccessary columns headed with 0. Is there a way to stop column creation when it hits a blank cell on the option page

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts March 12, 2008 15:07

  I just want to make sure that I understand. Options!$D$19:$D$70 contains a list of column headings. You want to use a NL("Columns") to create columns based on this list until you come to an empty cell within the range.
  
  Hopefully I've stated what you are trying to do, so I would use a combination of Jet Reports and Excel to accomplish this. Here is the formula that I created.
  
  =NL("Columns",INDIRECT(ADDRESS(ROW(Options!$D$19),COLUMN(Options!$D$19),,,"Options")&":"&ADDRESS(ROW($D$19)+COUNTA(Options!$D$19:$D$70)-1,COLUMN($D$19))))As you can see, this formula is almost all Excel and not Jet Reports. Basically, I used the following Excel functions
  

  INDIRECT ADDRESS ROW COLUMN COUNTA

  to create a Reference (ie Options!$D$19:$D$70), however I wanted the $D$70 portion to change based on the "COUNTA" of values contained in the range. Therefore, if cell Options!D29 is blank, then the reference would change to Options!$D$19:$D$29 automatically.
  
  This is a good example where it takes Excel and not Jet Reports to resolve the problem we are having.
• 0
  
  Jet Reports Historic Posts March 13, 2008 15:29

  Thanks
  
  That's what happens when you get tunnel vision and expect Jet to do it all for you <G>
  
  
  Van