0

Filter a Dynamic range from the options tab

Bob Thorp

February 12, 2019 19:33

None

Follow

How do I write the formula shown below to automatically update based on a filter selection (selecting CCO, CDO, CFO, etc) on the Options tab?  I can manually update the range on the report tab and the report runs fine, but would prefer not to go to design every time to update the range on the report tab. 

NL("Sheets",IF($D$2=2,"G/L Entry",Options!$D$48:$D$51),IF($D$2=2,"Department Code",""),IF($D$2=2,"0",""),IF($D$2=2,$L$19,""),IF($D$2=2,"Department Code",""),IF($D$2=2,$L$9,""))

FYI, the report runs on a department range on a single company or selected departments across multiple companies.

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis February 13, 2019 19:04

  Hi Bob -

  Here is how I would accomplish that.

  I would set up an options sheet and configure a Report Option for the user to select from my codes.

  I would also include a simple list of the codes (in alphabetical order) along with the start and end rows for the range associated with each code:

  

   

  Then, I would use Excel's VLOOKUP function to get the start and end row for the range:

  

   

  Next, I would add an NL(Columns) function to create a column for each cell in the range:

  

  and use Excel's INDIRECT function to get the value from that cell.

  Note the cell containing the INDIRECT function (and the next cell) have been assigned the Name Range "MyRange"

  

   

  When the report is run, this sheet would look like this:

  

  (for my example, I filled cells D48 through D100 with the integers 1 through 53 - thus cell 48 has a 1, cell 49 has a 2, etc.)

  Now, going back to your original function, I could modify the hard coded range with a reference to MyRange:

  NL("Sheets",IF($D$2=2,"G/L Entry",MyRange),IF($D$2=2,"Department Code",""),IF($D$2=2,"0",""),IF($D$2=2,$L$19,""),IF($D$2=2,"Department Code",""),IF($D$2=2,$L$9,""))

• 0
  
  Bob Thorp February 14, 2019 21:29

  Hi Harry,

  I set up the file as per your instructions.  The report works as expected when D2=2 (sheets generated for selected departments for a single company).  When D2=1, the first sheet calculates and the next sheet is generated, but cell E2 is blank on the second sheet and no other sheets are generated.  I'd appreciate any advise as to any necessary changes for E2 to populate for the additional sheets.

  Because my list of companies and departments is long, I set it up on a new tab called Multi and included the simple list of codes in alphabetical order with the start and finish rows (in red area) with the formulas in L3 and L4.

  The formulas from cells L3, L4 and the named range in L4-M4

  

  When the report is run, the company.department columns are generated

  

  E2 from the report tab with the Sheets formula.

• 0
  
  Harry Lewis February 14, 2019 23:31

  You are absolutely correct, Bob.  My method would work for an NL(Rows) function, but not for an NL(Sheets).

  The reason for this is that the NL(Sheets) function is performed BEFORE any of the other calculations take place.  I forgot.

  So, let's take another tack.

  In this example, I have defined MyRange as the Value cell in my Report Options

  

  I'm still doing the same LOOKUP for my CCO, CDO, etc.

  BUT...

  Now I have to define a range for each of the possible values that could be in the LOOKUP. 

  For my simplified example:

  CCO comprises cells B11 through B14

  CDO:  cells B15 through B20

  CFO:  cells B21 through B25

  CPO:  cells B26 through B36

  

   

  All of this is on my options sheet.

  In my actual report, I simply have the function:

  =NL("Sheets",INDIRECT(MyRange))

   

  So:  I have an NL function containing an INDIRECT to a cell which contains the name of the Named Range which contains the value I want to use for my sheet names.  (yes, I know... a bit convoluted).

  When I run my report and choose CFO, I get these sheets:

  

  If I choose CCO:

  

   

  etc.

  I believe, therefore, that your function could used the INDIRECT(MyRange) technique [once all your Named Ranges are set up].

• 0
  
  Bob Thorp February 15, 2019 16:27

  Thanks Harry.  I really appreciate all your help with this report.  Using the INDIRECT function and the named ranges made the formula  on the report tab able to update and the report finally does everything I wanted it to do.  Thanks again for your assistance and patience as you worked through my many questions over the past days!!!