Sourcing data from a table of information.

[Behlen]

Okay, so here goes. I'm trying to find out how to setup worksheet that can source data from a list data base of information.

Wanting to make it easy for others to make two selections from seperate drop-down lists, then based on those selections, return the value in the table of values. My table below is just a sample, based on what I want to acheive without copying sensitive data. So what I want to do is have page with the following:

Month...............Type of Soda..............Consumed
(1st drop-list)....(2nd drop-list)............"Returned Value"


Then the table below could be on the same page (but hidden), or on a another page. My thought is the user picks from the first drop down list and selects the month (in this case the choices should be January, February, or March - 3 lines only). Then once selected when they pick from the second drop down list the choices are itemized based on the corresponding month selected in the first (so if January picked, the second list only shows sodas listed in January). After making a selection from both lists, the value is returned from the combined line values. So I if picked "February" and "Pepup", it would return a value of 910. Hope that makes sense.

Type of Soda.........Month........Consumed
Pepup..................January......946
Splash.................January......715
Diet Splash...........January.....506
Pepup..................February....910
Diet Pepup...........February.....894
Diet Splash..........February......276
Pepup..................March.........840
Diet Pepup...........March.........442
Splash.................March.........109
Diet Splash...........March.........263

I know how to make drop down lists, and simple things. But I figure I need a formula or something to set the parameters of how to source the information i'm looking for. any help is appreciated. Thanks.

[FDibbins]

Hi and welcome to the forum

	A	B	C
1	Type of Soda	Month	Consumed
2	Pepup	January	946
3	Splash	January	715
4	Diet Splash	January	506
5	Pepup	February	910
6	Diet pepup	February	894
7	Diet Splash	February	276
8	Pepup	March	840
9	Diet pepup	March	442
10	Splash	March	109
11	Diet Splash	March	263
12
13	Month	Type	Consumed
14	January	Pepup	946

iN c14, USE THIS array FORMULA...
=INDEX($C$2:$C$11,MATCH($B$14&$A$14,$A$2:$A$11&$B$2:$B$11,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Behlen]

Sorry, I guess I wasn't quite clear enough in my explaination. Thank you for the part you did answer for me, that will work, I just need a bit more help before that step is made.

I should've said while I know how to make a simple drop-down lists from the table, I don't know how to minimize that list. From the example above, I'd be making the drop-down list in cell A14 for the month. Making a simple list would create: January, January, January, February, February, February, March, March, March, March. Instead I'm wanting to know how to create a list that only lists the unique months, such as only listing: January, February, March (3 choices, instead of 10 with multiples).

The second part maybe more trickier....not sure. But after selecting the month, the second pick list created in cell B14, would only show choices related to the month chosen in cell A14. I want it to look back at the table and any row matching the Month chosen first, then would list only those choices that correspond to that month. So if I was to pick February in A14, choices in the B14 list would only show Pepup, Diet Pepup, & Diet Splash (nothing else).

Does that clear things up? Does that make more sense? Is this possible?

(Reason being is that my actual table has hundreds of rows, so by minimizing/sorting/...or whatever the correct term is it will make the user's choices more relevent.)

Thanks for any additional help that can be offered.

[FDibbins]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Behlen]

Here is a copy of my sample workbook, original data in columns A, B, C and the search results in E, F, G. I just made this one, so there is no Before and After. This is just purely generic, as the actual workbook is very long.....but the theory behind setting up the workbook will be the exact same.

The one thing my previous post didn't have is that the month column isn't always cronological, so I've mixed things up in the attachment to more acurately represent what I have in my actual workbook.

Let me know if adding the attachment didn't work.

Book1.xlsx

[FDibbins]

Instead of using the data in A as the range for your DD, you could either create a small table with all the months, andthen reference that, or you could type in all the month names IN the DV list...January, February, March, April, May etc...

Also, please note that you have trailing spaces on your months - as many as 4 for March - (at least, in the sample data), you will need to remove those if you want to use the above suggestion. If that is impractical, you could adjust my formula to this...
=INDEX($C$2:$C$25,MATCH($E$2&"*"&$F$2,$B$2:$B$25&$A$2:$A$25,0))
ARRAY entered as above

[newdoverman]

I'm not quite sure exactly what you are looking for but here is your worksheet with drop down lists for the month and that will address a list that has the choices available for that particular month and return the appropriate value from column C.

[Behlen]

Thanks FDibbins, didn't quite get what I was looking for. But on the other hand you got me thinking more about doing something more with range functions, and I figured out a solution by doing some re-formatting.

Newdoverman, thanks for posting, but not what I was looking for.

Problem solved so this thread can be closed. Thanks again. Cheers.

[newdoverman]

You're welcome. Glad to have helped a bit.

Thanks for the feedback.

[FDibbins]

Yes, what NDM said