Home

Issues With Advanced Filter - Using Formulas Referencing Other Cells

Aylio
Occasional Visitor

I'm trying to filter a list of of value ranges based on whether a value entered into a cell is within the ranges. After doing some research it seems my best option is to use Excel's Advance Filter feature. For some reason though advanced filtering works perfectly fine for this purpose when I use formulas that reference a specific number (example: "=340>=A5", List range A4:X50), but if I replace the specific number with a cell that contains that number the filter filters out every row in my list (Example: "=A1>=A5" where cell A1 contains 340). Is this just a limitation of the program or is there another way I can achieve the desired functionality (perhaps using a shadow table that is filled in via a macro that would fill in A1's contents into the formula)? Thanks a lot for any help!

P.S. If needed I can create a mock up of exactly what I am trying to accomplish. 

2 Replies

Hi, @Aylio.  A mock-up would be helpful. I am not quite following what you need. 

For now, my suggestion would be to name the cell A1 that contains the value and the use the name on the formula.

For example, name A1 as "MyValue" and then use the formula "=MyValue>=A5" 

I am not sure if this would work. It's just an idea that might work.

Hi @Aylio ,

 

Please use absolute reference

=$A$1>A5

Advanced Filter applies filter criteria one by one to each cell in the range. If you use relative reference it'll be =A1>A5 for the A5; for next cell down =A2>A6, etc. 

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies