All possible combinations from dynamic list

Hi,

I'm trying to generate alternate criteria sets from a given criteria set, in this case, for real estate.

I have formulas that vary input criteria and generate unique lists. For example, for Price, I do increments of $50k up to 2x input, for Bedrooms, I vary 2 up and 1 down.

So if I input $100,000 - $200,000 with 3 Bedrooms, I get the following lists:

$150,000 - $250,000
$200,000 - $300,000
$250,000 - $350,000
$300,000 - $400,000

and

2 Bedrooms
4 Bedrooms
5 Bedrooms

So the alternate criteria sets for the input criteria set are:

2 Bedrooms, $150,000 - $250,000
2 Bedrooms, $200,000 - $300,000
2 Bedrooms, $250,000 - $350,000
2 Bedrooms, $300,000 - $400,000
4 Bedrooms, $150,000 - $250,000
4 Bedrooms, $200,000 - $300,000
4 Bedrooms, $250,000 - $350,000
4 Bedrooms, $300,000 - $400,000
5 Bedrooms, $150,000 - $250,000
5 Bedrooms, $200,000 - $300,000
5 Bedrooms, $250,000 - $350,000
5 Bedrooms, $300,000 - $400,000

In the real case, I have not two lists to combine but six: Price, Bedrooms, Bathrooms, Half-Baths, Square Footage, and Acreage, resulting in possible combinations in the 20-30,000 range, each with changing lists based on inputs. I considered implementing this formulaically, and would still prefer that, but my attempts were too calculation-heavy and brought my system to a halt.

My gut says that this has been done many, many times before, so I am trying to avoid reinventing the wheel. Anyone able to help?

@OllyXLS

Sorry for the delayed response, I don't seem to be getting notifications.

I was aware of the Query function, but I didn't know that I could just refresh on it! That's great!

I'm still having a challenge though. My dynamic fields vary in number of rows. I found that the Cartesian Sets are made from the number of rows in the named range. So if I have, say, 20 rows in each Named Range for 3 Named Ranges, my total number of possible combinations would be 8000, and the query will produce 8000 combinations, even if there's only data in 3 rows.

How do I get around this, so I only have the query looking at cells with values in them?

I'll send this via PM as well.

Just discovered how to change the parameters in a Microsoft Query to look at only "Not Null". However, I have found this doesn't work with cells that have a formula but are blank. So I'm experimenting with using pivot tables as sources. We'll see how that goes.