Populating data based on multiple criteria

[JTNuke]

Quick question for the pros here! I am trying to populate data into a cell based on two dropdown menus. For example, if dropdown menu one is selected as "ALPHA" and dropdown menu two is selected as "alpha" then information will populate to the chosen cell (call it B2) from a different sheet and cell (call it sheet ALPHAalpha cell C1). There are many combinations here and I do not know how to structure the formula to allow for each of them (one at a time) to populate based on the chosen dropdown menu selection. Any ideas?

 

[etaf]

can we have some more examples

the Sheetname is based on the 2 dropdowns then
But how are the cells chosen

so in B2 you would have an indirect() fomula to choose the sheetname
something like

Dropdown1 in A1 , Dropdown2 in B1

indirect( "'"&A1&B1&"'! ... range )

see here, where i have a sheet called AlphaAlpha , and in cell C3 on that sheet i have FRED

Book1
	A	B
1	Alpha	Alpha
2		FRED
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INDIRECT("'"&A1&B1&"'!C3")

 

[JTNuke]

Okay lets start over because I think I did a bad job explaining what my goals are. I have attached two pictures and if this doesn't provide enough information I will upload the mini-sheet program when I am home and not on a work computer.

I am creating a user guide for different products. This guide will allow you to choose a product "product selection" and specify the information category you are looking for "Information selection". Each one of the options under "information selection" has its own sheet with a corresponding name. Additionally, each one of those sheets lists all the drug products that are available to be chosen under "product selection".

For example the "take orders" sheet has all the drug products that are available under "product selection" with corresponding information filled out. The flow for the user would look like this:
User selects "Home" sheet >> user chooses a product from "product selection" >> user chooses desired information category from "information selection" >> user looks under "general information" (currently merged cells but I can change it if needed) to see information that has populated from the corresponding sheet and product.

More specific example:
User selects "Home" sheet >> user chooses Datscan from "product selection" >> user chooses take orders from "information selection" >> user looks under "general information" to see Wednesday only has populated from the corresponding sheet (order entry) and product/row (B3).

As I build this out I plan to add paragraphs of text and information. I need the user to be able to simply select the drug product and information category and reference the general information section to see what they are looking for. Eventually, I do want to add a "all information" option under the "information selection" which would ideally populate information for each information category for the chosen drug product.

 

[JTNuke]

can we have some more examples

the Sheetname is based on the 2 dropdowns then
But how are the cells chosen

so in B2 you would have an indirect() fomula to choose the sheetname
something like

Dropdown1 in A1 , Dropdown2 in B1

indirect( "'"&A1&B1&"'! ... range )

see here, where i have a sheet called AlphaAlpha , and in cell C3 on that sheet i have FRED

Book1
	A	B
1	Alpha	Alpha
2		FRED
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INDIRECT("'"&A1&B1&"'!C3")

Thank you for the quick response! I think I could have done a better job explaining my goals so I updated my posting. Please let me know if this helps you help me!

 

[etaf]

looks quite complicated now - as you would need the dropdowns populated based on content on different sheets as the user navigates and then you are returning multiple results - i think thats what you suggesting , but i still may not follow and images are not very good to work on
A sample file NOT the whole thing , but a good representation , and the expected results will help members here answer, not sure i will be able to
Merged cells in my experience are almost never useful for formulas or data extraction , they are are OK on a flat file for viewing only
So if you can remove all merged cells.
and look to see if you can add using XL2BB

 

[JTNuke]

Product ordering (BETA).xlsx
	A	B	C	D	E	F
1		8/18/21 19:38	Choose a product and information selection from to be directed to the appropriate information
2
3		Product selection		Ä
4
5		Information selection		Ä
6
7		Links	Tools		General Information
8
9
Home

Cell Formulas
Range	Formula
B1	B1	=NOW()
D3,D5	D3	=HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))

Cells with Data Validation
Cell	Allow	Criteria
C5	List	='Data entry'!$D$3:$D$18
C3	List	='Data entry'!$B$3:$B$21

 

[JTNuke]

looks quite complicated now - as you would need the dropdowns populated based on content on different sheets as the user navigates and then you are returning multiple results - i think thats what you suggesting , but i still may not follow and images are not very good to work on
A sample file NOT the whole thing , but a good representation , and the expected results will help members here answer, not sure i will be able to
Merged cells in my experience are almost never useful for formulas or data extraction , they are are OK on a flat file for viewing only
So if you can remove all merged cells.
and look to see if you can add using XL2BB

okay I uploaded the main page of the workbook that I am working on. I noticed the dropdown menus are not working on the uploaded version. Please let me know if you need this information. Thanks again for everything.