I am trying to create an options page to allow the end user to specify a date range to be used as a filter. One of my cells currently has this:=NL("rows","Quotes - Original",{"Aircraft ID","Trip Kid - comm","Trip Kid - date","Trip Kid - time","Trip Kid - user","Trip Kid - mult","KID - mult","KID - date","KID - comm","KID - time","KID - user","Pax KID - comm","Pax KID - mult","Pax KID - user","Pax KID - time","Pax KID - date","Rate Type Code","AcRate KID - mult","AcRate KID - date","AcRate KID - time","AcRate KID - user","AcRate KID - comm","Confirmation Number","Pass. Flight Time","Deadhead Flight Time","Trip Departure Date","EFT","Time Quoted"},"+Trip Departure Date", "41060..41090", "Aircraft ID","'"&D3&"'","Trip Kid - time","1..")
I updated that to being=NL("rows","Quotes - Original",{"Aircraft ID","Trip Kid - comm","Trip Kid - date","Trip Kid - time","Trip Kid - user","Trip Kid - mult","KID - mult","KID - date","KID - comm","KID - time","KID - user","Pax KID - comm","Pax KID - mult","Pax KID - user","Pax KID - time","Pax KID - date","Rate Type Code","AcRate KID - mult","AcRate KID - date","AcRate KID - time","AcRate KID - user","AcRate KID - comm","Confirmation Number","Pass. Flight Time","Deadhead Flight Time","Trip Departure Date","EFT","Time Quoted"},"[b]+Trip Departure Date",Options!D3[/b], "Aircraft ID","'"&D3&"'","Trip Kid - time","1..")
~Edit I tried to bold around the change but it doesn't appear when formatted as Code. i changed the filter applied to the +Trip Departure Date field from being hard coded to referencing Options!D3.
However, if I put 41060..41090 into Cell D3 on sheet Options I get the following error:
Empty Filter Not Allowed
It does return one correct row, but it seems as if it is automatically trying to look into cell Options!D4 for the next rows date filter. Almost as if it is iterating through the cells in the options page as it does in the report page. Would there be a way to correct this?
Date
Votes
6 comments
-
Jet Reports Historic Posts I'm afraid I don't quite understand what you're saying. You say it returns one correct row, but you get an "empty filter not allowed" error? What cell is that error coming from? It can't be coming from the NL(Rows) formula if it's returning a correct row of data, so I'm confused. That error would have to be coming from a Jet function which is returning #VALUE.
Regards,
Hughes -
Jet Reports Historic Posts Ok, I've trimmed it down a bit.
In cell E3 I have this function in design mode:=NL("rows","Quotes - Original",{"Aircraft ID","Trip Departure Date","EFT","Time Quoted"},"+Trip Departure Date",Options!D3, "Aircraft ID","'"&D3&"'")
It should be selecting the values from Aircraft ID, Trip Departure Date, EFT and TimeQuoted from the table Quotes - Original, where Trip Departure Date is between the values in Options!D3, and Aircraft ID = the value in D3 (Generated from another NL function).
When I click on report, it grabs the values from Options!D3 and applies it as a filter to "Trip Departure Date" and it displays the data for everything else on row 3. However when it expands the NL function in column E, it then places this as the NL function being ran in cell E4:=NL("rows","Quotes - Original",{"Aircraft ID","Trip Departure Date","EFT","Time Quoted"},"+Trip Departure Date",Options!D4, "Aircraft ID","'"&D4&"'")
See how it changed the lookup from the Options page from Options!D3 to Options!D4? Because it is incrementing the row number that it is looking for the date range from, it is looking into a cell without any value and that is causing the blank filter error.
I came across another issue that I am having but it is not related. I am also trying to conditionally hide rows. In cell B1 I have Hide+. Then in cell B3 I have the function=IF(U3=0,"Hide","Show")
The IF function is returning the correct value, it is showing Hide when U3 = 0, however the row is not being hidden.
Please let me know if I should open a second thread, however it seems like you are the one answering everything anyway. -
Jet Reports Historic Posts Oh right this is because you have another replicator on this same row to the left of this function. You need to change your cell referencing to the options sheet to an absolute cell reference like this:
=NL("rows","Quotes - Original",{"Aircraft ID","Trip Departure Date","EFT","Time Quoted"},"+Trip Departure Date",Options!$D$3, "Aircraft ID","'"&D3&"'")
The exact same thing that happened in Jet would have happened if you had just copied the whole row and pasted it into the next row down. If a cell address is relative then Excel will update it when you copy or move it to another cell. I'm assuming you want this behavior for Aircraft ID since it needs to refer to the ID on the same row, but you don't want it for the options sheet reference.
The correct thing to have in cell B1 for conditional hide is Hide+? so you're just missing the ? I think.
Regards,
Hughes -
Jet Reports Historic Posts Once again, thanks you have been very helpful and that corrected both of the issues. Now I have one more, hopefully my last.
I would like to add one blank row in between every group of results.=NL("Rows","Quotes - Original",{"Aircraft ID","Confirmation Number","Pass. Flight Time","Deadhead Flight Time","Trip Departure Date","EFT","Time Quoted"},"+Aircraft ID","*","+Trip Departure Date",Options!$D$3)
Is the NL Code, It is ordered by Aircraft ID and then by Trip Departure Date. Is there a way, that every time the Aircraft ID changes it enters a blank row?
I tried Rows=2 in it, but it adds a blank row after every row.
I tried to use that and then conditionally show the blank row if the Aircraft ID in the row above it was the same as the aircraft ID in the row below it, but when it replicated the rows is threw off this relative reference.=IF(D3<>D5,Show,Hide)
into=IF(D3<D112,Show,Hide)
I have also tried dynamically creating the cell reference with this:=IF(D3=OFFSET(D3,2,0),Show,Hide)
But I can't seem to get OFFSET to work nested in an if function.
Just doing OFFSET(D3,2,0) does put into that cell the value of D5, but trying to nest it in an if function results in #NAME?
Ignore this please, I was missing quotation marks in the IF function. using the offset method worked. -
Jet Reports Historic Posts Hi,
In order to do this, you would have to split up your NL(Rows) formula into 2 formulas. One would be NL(Rows=2) and just retrieve the Aircraft ID. Then the 2nd formula would be NL(Rows) and retrieve the rest of the fields, filtering on the Aircraft ID from the previous formula. Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts Yes that worked, thanks.
Please sign in to leave a comment.