Unanswered: Reference Filtered Text in a Column in Excel in other formulas
I have a question about trying to reference text in Excel that I have already filtered, and then using those filtered text values in formulas somewhere else. Those formulas I don't think are the important part of the question. For example, if I have 2 columns of data:
City | Street
New York | Fifth Avenue
New York | Broadway
Chicago | Michigan Ave
Chicago | Lakeshore
Boston | Newbury
Say I filter Chicago and Boston, so the New York data no longer shows. This is a simple example, but just FYI the database I have could be filtered multiple times more with many more columns. But really at the end of the day, once I filter, I'd like to run all my formulas from the "Street" column (the unique values).
I'd like to use some SUMIF or VLOOKUP functions on another sheet and reference what is now displayed here as the criteria (now Michigan Ave, Lakeshore and Newbury) - what I just filtered. As this will be the criteria in these formulas, I guess I need these values pasted somewhere else.
I tried looking at the Advanced Filter with Macros, but it seems that the Macro you setup can only work from one criteria for each column (ie. only filter what cities start with "C"... or what States start with "N" if I had another column, etc.).
For convenience, the key for me is to use the regular filtering option in Excel, and then somehow have the filtered, unique values show up somewhere else where I can reference them as criteria in formulas. And it would help if this is dynamic and not complicated for everyday use, as I (nor the people I work with) are very good at macros!