What's wrong with my INDIRECT set-up

[liloo]

Hello,

I'm trying to create a workbook for budget planning. Each event will have different participation levels, such as Attendee, Exhibitor, Sponsor, etc. Each level has its own associated cost. Instead of creating a huge sheet with each level as a row for each event, I believe INDIRECT could be used in this case.

I followed and practiced with a few online samples and they work. I'm not sure why it's not working once I input the data I need.

In my workbook, I created 2 tabs for Data Entry and List. In the List tab, I named B3:B6 range as Participation and D3:D6 range as Cost. On the Data Entry tab, I made a table and in B3, I selected Data Validation and used "=Participation" as the value. So far so good. Then in C3, I selected Data Validation and used "=INDIRECT(B3)". No good; got "#REF!".

What am I doing wrong? Is it due to the symbols used in Cost range ($, *, +) ? I took out spaces so they are all technically one word.

Any guidance is much appreciated.

 

[awoohaw]

Maybe I am misunderstanding, but you don't need indirect Just a lookup
Regarding named ranges though, I think you need to specifiy if they are valid for the workbook or only a worksheet.

How does this look:

Mr Excel 3.xlsm
	A	B	C	D	E
1
2		Participation Options		Cost
3		Attendee		$99
4		Exhibitor		$59*
5		Exhibitor Speaker		$59*+1,750
6		Sponsor		$3500
7
8
9		Participation		Estimated Cost
10		Sponsor		$3500
11
Sheet1

Cell Formulas
Range	Formula
D10	D10	=IF(B10="","",XLOOKUP(B10,Participation_Options,Cost))

Named Ranges
Name	Refers To	Cells
Cost	=Sheet1!$D$3:$D$6	D10
Participation_Options	=Sheet1!$B$3:$B$6	D10

Cells with Data Validation
Cell	Allow	Criteria
B10	List	=Participation_Options

 

[liloo]

Hi awoohaw,
You are right. I've tested it on a small portion of my worksheet and it's working. I'm going to look into how to named ranges for the workbook.
I shall return to provide an update once I try it on my entire list of events.
Thank you!

 

[awoohaw]

Great, happy to help. Best wishes!