Dynamic Data Validation List

[CorrelateSpilled]

Hi,

I am using the web version of Excel. I have a workbook with the following sheets:

• FINANCIAL_ACCOUNT sheet with the following table structure. Of note, the Currency column can be a single currency or comma separated list of currencies.

D​	E​
Account Name​	Currency​
acct-name-01​	CAD​
acct-name-02​	USD​
acct-name-03​	CAD, USD​

• TRANSACTION_DESCRIPTION sheet with the following table structure. Of note, the Currency column contains the currency that is used to buy/sell the item. It will always be a single currency; not a comma-separated list.

A​	F​
Transaction Description​	Currency​
XNYS:SHOP​	USD​
XTSE:SHOP​	CAD​
XTSE:T​	CAD​
XNAS:AMD​	USD​
XNAS:INTC​	USD​

• FINANCIAL_TRANSACTION sheet with the following table structure:

D​	F​
Financial Account​	Transaction Description​
acct-name-01​	XTSE:SHOP​
acct-name-02​	XNAS:AMD​
acct-name-03​	XTSE:T​
acct-name-03​	XNAS:INTC​

The Financial Account column is a data-validated list that gets its data from the FINANCIAL_ACCOUNT sheet. What I would like, but have not figured out how to do yet, is for the Transaction Description column to also be a data-validated list that is dynamically generated depending on the Financial Account that is selected. So when a Financial Account is selected, then only Transaction Descriptions whose Currency is in the Financial Account's Currency would be able to be selected.

So when acct-name-01 is selected, then the only Transaction Descriptions that are available to be selected should be XTSE:SHOP and XTSE:T. And if acct-name-02 is selected then the Transaction Descriptions that are available to be selected should be XNYS:SHOP, XNAS:AMD, and XNAS:INTC. Lastly, if acct-name-03 is selected then all Transaction Descriptions should be available to be selected.

But I am stumped on the correct formula to do this. Any help pointing me in the right direction would be much appreciated. Thanks for your time!

 

[Pecoflyer]

Ho and welcome. Please post a sample sheet (no pics please) with some desensitized data. Thanks

 

[CorrelateSpilled]

Ho and welcome. Please post a sample sheet (no pics please) with some desensitized data. Thanks

Hi, thanks for the reply. I have attached a copy of my workbook with some random desensitized data and a couple of notes in some of the sheets. I only have the web version of Excel so I'm not sure if all the data got preserved when downloading it to my computer. Thanks for taking the time to take a look at it!

 

[p45cal]

This is only a step towards what you are looking for; hopefully someone might improve on it.
At cell T3 of sheet FINANCIAL_TRANSACTION in the attached is a formula. It can be copied down, but it cannot be part of the table. It produces a list of transaction descriptions.
Data validation in column F refers to the cell in column T in the same row, with its attendant # postfix to refer to the dynamic range.
I was hoping to be able to put that formula (without its TRANSPOSE envelope) directly into data validation but DV won't allow the lambda element (it's the OR in the BYROW function). I've run out of time looking for a non-lambda equivalent.
I can't even use TEXTJOIN in column T, to reduce the columns used to 1 (to be able to incorporate the column into the table and thus avail myself of formula self-propagation) because DV doesn't play when I try to use TEXTSPLIT!

 

[CorrelateSpilled]

Hi,

Thanks for taking the time to take a look at my problem. I have taken a look at the output of your formula but it looks like it does not filter the Transaction Descriptions correctly. For example, Row 2 of the FINANCIAL_TRANSACTIONS sheet has Financial Account acct-name-01, which has CAD as its Currency however one of the Transaction Descriptions that your formula outputs is XNAS:AMD which is USD Currency.

But based on your formula I was able to figure out a formula that filters the Transaction Descriptions table based on the selected Financial Account:

=FILTER(TBL_TRANSACTION_DESCRIPTION[Transaction Description], ISNUMBER(SEARCH(TBL_TRANSACTION_DESCRIPTION[Currency], XLOOKUP(TBL_FINANCIAL_TRANSACTION[@[Financial Account]], TBL_FINANCIAL_ACCOUNT[Account Name], TBL_FINANCIAL_ACCOUNT[Currency], "No matches", 0, 1))), "No matches")

But I get an error when adding this formula to data validation. Another option I tried is adding a column to the table in the FINANCIAL_ACCOUNTS sheet with this formula that gets the valid Transaction Descriptions for that Financial Account:

=FILTER(TBL_TRANSACTION_DESCRIPTION[Transaction Description], ISNUMBER(SEARCH(TBL_TRANSACTION_DESCRIPTION[Currency], TBL_FINANCIAL_ACCOUNT[@[Currency]])), "No matches")

But when trying to point the Data Validation to this column using an XLOOKUP formula I get an error.

Any help is much appreciated. Thanks for your time!

 

[p45cal]

Hi,

Thanks for taking the time to take a look at my problem. I have taken a look at the output of your formula but it looks like it does not filter the Transaction Descriptions correctly. For example, Row 2 of the FINANCIAL_TRANSACTIONS sheet has Financial Account acct-name-01, which has CAD as its Currency however one of the Transaction Descriptions that your formula outputs is XNAS:AMD which is USD Currency.

Quite so, the formula should have been

=TRANSPOSE(FILTER(TBL_TRANSACTION_DESCRIPTION[Transaction Description],BYROW(TBL_TRANSACTION_DESCRIPTION[Currency]=TRIM(TEXTSPLIT(XLOOKUP(TBL_FINANCIAL_TRANSACTION[@[Financial Account]],TBL_FINANCIAL_ACCOUNT[Account Name],TBL_FINANCIAL_ACCOUNT[Currency]),",")),OR)))

Still thinking about it; it seems DV doesn't like quite a lot!
viz.

Data validation using VSTACK with dynamic arrays | Microsoft Community Hub

I create two distinct dynamic arrays (e.g. dynArray1=OFFSET(rangeName1,0,0,COUNTA(rangeName1),1). Either of the two dynamic arrays can be used...

techcommunity.microsoft.com