Dynamic Dropdown List Error

[Legacy 464248]

Hi All,

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached screenshots for peoples reference, but to provide some clarify, the formula in question appears on the tab named "Expense Input" but references a table on the tab named "Setup". I have TRIED to paste the XL2BB code below, but I have not used it before, so I am unsure if it is not correct or enough information.

Any ideas or help would be greatly appreciated.

Expense Input Tab:

Household_Budget_Upload.xlsx
	B	C	D	E	F	G	H
19	Expense Category	Expense Sub Category	Merchant	Expense Description	Date Of Expenditure	Expense Amount	Tax Deduction?
20	Savings	#VALUE!
21	Loans & Debts	#VALUE!
Expense Input

Cell Formulas
Range	Formula
C20:C21	C20	=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

Cells with Data Validation
Cell	Allow	Criteria
B20:B21	List	=Expense_Category

Setup Tab:

Household_Budget_Upload.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Income	Savings	Loans & Debts	Home Expenses	Work Expenses Person 2	Work Expenses Person 1	Education & Lessons	Transportation	Health	Charity & Gifts	Daily Living	Entertainment	Subscriptions	Fees & Penalties	Miscellaneous
4	Net Salary - Person 1	House Deposit	Credit Card	Rent	Courses - Person 2	Courses - Person 1	Swimming Lessons - Baby	Fuel - Car 1	Health Insurance	Birthday Gifts Given	Groceries (Food Only)	Wine Investment	Newspaper	Bank Fees	Postage
5	Net Salary - Person 2	Car Replacement	HECS - Person 2	Internet	Books - Person 2	Books - Person 1	Other	Fuel - Car 2	Doctor	Christmas Gifts Given	Toiletries	Sports & Recreation	Magazines	Traffic Infringements	Miscellaneous Spending
6	PAYG Tax Adjustment - Person 1	Retirement Fund	Home Mortgage	Mobile - Person 2	Professional Memberships - Person 2	Professional Memberships - Person 1		Car Insurance - Car 1	Dentist	Misc Gifts Given	Household Consumables	Alcohol	Dues/Memberships	Late Utility Fees	Other
7	PAYG Tax Adjustment - Person 2	Investments	Other	Mobile - Person 1	Indemnity Insurance - Person 2	Licensing - Person 1		Car Insurance - Car 2	Misc Services	Charitable Donations	Personal Supplies	Games	Streaming Services	Other
8	GST Credit - Person 2	Education Fund		Home Maintenance	Work Tools - Person 2	Work Tools - Person 1		Registration - Car 1	Medicine/Drugs	Other	Clothing - Person 1	Dining / Eating Out	Storage / Backup Services
9	Interest Income	Personal - Person 1		Misc Tools	Alterations / Laundry - Person 2	Alterations / Laundry - Person 1		Registration - Car 2	Gym Membership		Clothing - Person 2	Dinner Parties	Other
10	Refunds / Reimbursements	Personal - Person 2		Furniture	PPE - Person 2	Uniform - Person 1		Licensing - Person 1	Life Insurance		Clothing - Baby	Hobbies
11	Gifts Received	Other		Appliances	Other	Other		Licensing - Vivane	Other		Toiletries / Amenities - Baby	Media
12	Other			Other				General Maintenance			Cleaning Supplies	Toys/Gadgets
13								Repairs			Professional Services	Vacation/Travel
14								Misc Accessories			Daily Dining / Eating Out	Other
15								Uber / Taxi / Ride Share			Personal Grooming - Person 1
16								Other			Personal Grooming - Person 2
17											Other
18
19
20
21
Setup

 

[Fluff]

Hi & welcome to MrExcel.
How about
=OFFSET(Setup!$D$3,1,MATCH(B20,Setup!$D$3:$Q$3,0)-1,COUNTA(OFFSET(Setup!$D$3,1,MATCH(B20,Setup!$D$3:$Q$3,0)-1,20,1)),1)

 

[Legacy 464248]

Hi & welcome to MrExcel.
How about
=OFFSET(Setup!$D$3,1,MATCH(B20,Setup!$D$3:$Q$3,0)-1,COUNTA(OFFSET(Setup!$D$3,1,MATCH(B20,Setup!$D$3:$Q$3,0)-1,20,1)),1)

Hi Fluff,

Thanks for the quick reply! That formula works well for what I need it for. I had originally thought referencing named ranges in the table would be more concise, but I don't know if that is possible?

It seems that I can only make it work with absolute cell references. Is that correct?

 

[Fluff]

You cannot use table references in Data validation, although you can use named ranges.

 

[Legacy 464248]

You cannot use table references in Data validation, although you can use named ranges.

Hi Fluff,

Thanks for the info. Perfect solution and nice and quick!

 

[Fluff]

You're welcome & thanks for the feedback.

For future reference, while we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Cross posted Dynamic Dependant Dropdown Error

 

[Legacy 464248]

Thanks Fluff,

Noted and I will make sure I include that in future. Thanks again.