populate from drop list with multiple tables

[price83]

Hi All,

i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on another sheet that relate to the selected drop... is this possible?

 

[price83]

main sheet

Book1
	B	C	D	E
1	Site	Surveys completed	Average engineer experience supporting site? ( 1 - Hate it, 5 - Love it)	Comment on overall experience
2
3	Multiple	2	4.50
4
Data

raw data sheet

Book1
	A	B	C
22	Which Customer site are you based at?	How would you rate your overall experience supporting your site? ( 1 - Hate it, 5 - Love it)	Comment on overall experience
23	Multiple	4	I really enjoy working on most sites, the only issue is documentation and access.
24	Multiple	5
RAW_Data

 

[Fluff]

Ok, how about
=INDEX(INDIRECT($B$1),0,3)

 

[price83]

nothing seemed to happen with that.

is it not possible to have more than 1 indirect formula for the same subject?

 

[Fluff]

You can have as many as you like.
Can you upload a copy of your workbook to a file share site, such as OneDrive, DropBox, Google Drive. Then mark for sharing & post the link to the thread?

 

[price83]

UK Campus Engineer Feedback(1-32).xlsx

drive.google.com

 

[Fluff]

Ok, thanks for the file.
Rather than changing the names of the tables, you have created named ranges instead. You need to delete those names & then select a cell inside one of the tables, select "Table design" tab on the ribbon & then in the Table Name box rename the table so it matches the relevant value from the drop down.

Then in D3 use
=AVERAGE(INDEX(INDIRECT(B3),,2))
and in E3 use
=INDEX(INDIRECT(B3),,3)


You would also be advised to resize all the tables, so that they do not cover all 16,384 columns. Then delete columns Y:XFD & save, that way your workbook will open a lot faster.

 

[price83]

i am so thankful for your help today i have learnt a lot and appreciate your time with this, have a great weekend

 

[Fluff]

Does that mean you have it working now?

 

[price83]

i do thanks

 

[Fluff]

Brilliant, glad it's working & thanks for the feedback