Advanced Filter - calculated reference as the criteria

[delaing]

I'm trying to use Advanced Filter to show me the records where the REVPAR is between a calculated range of values.

If there is an easier way than what I have setup, please suggest as much. I am wanting to see records where the REVPAR is less than/greater than a percentage of a specific REVPAR value (F4). Since I cannot figure out how to do this within the Criteria Range, I've established two cells (O3 & P3) which show the upper/lower limits of the value range I'm restricting within.

To the Filter, I'm saying: Show me the records where the REVPAR is within a 20% plus/minus bracketing of the Subject's REVPAR (F4).

I tried using a Formula Reference as the criteria (O6 & P6) as suggested on various Internet sources, but cannot get it to act as a less than/greater than query.

I've attached a test xlsx for reference.

Let me know where I need to clarify on this request.

Thank you,
Delain

[Richard Buttrey]

Hi

Change
O2 & P2 to read 'RevPar'

O3:

Please Login or Register  to view this content.

P3:

Please Login or Register  to view this content.

Now with the Advanced Filter use O2:P3 as the criteria range.

Here's a macro that will do it for you

Please Login or Register  to view this content.

[delaing]

Such a beautiful thing!

Thanks Richard. I had a feeling there was a particular syntax with the < and > operators I didn't understand; didn't see anywhere else suggesting that you can concatenate with a formula string in the criteria.
And the macro snippet will definitely get used.

Thank you,
Delain