If small formula help?

[samyscraps01]

Hi, can i please get some help. I am trying to work out this formula, similar to the one i used earlier but now altered. In cell A2 I have concatenated data from cells A5 and B5. I did this because I have a mix of numbers and text. That formula lives in cell A2. In the category cell B4, that is a drop-down list with multiple categories, the same goes with cell A5, those will have multiple employees. Basically, what I would like to do is this, if I go to drop-down A5 and select a name, then go to category B5 and select anything in this case delivery on cell C5 give me the dates that are associated with that data. I know it needs to reference a second spreadsheet which will look like the chart next to this one.

Spreadsheet X
[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]A2 Concatenated Data A5, B5[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4 EMPLOYEE[/TD]
[TD]B4 CATEGORY[/TD]
[TD]C4 DATES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A5 204Smith[/TD]
[TD]B5 Delivery[/TD]
[TD]C5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet X, tab XYZ
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Delivery[/TD]
[TD]Pick-Up[/TD]
[TD]Purchase[/TD]
[/TR]
[TR]
[TD]204Smith[/TD]
[TD]1/12/18[/TD]
[TD][/TD]
[TD]2/10/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








I would like something like this, I select 204Smith from the A5 drop-down menu and then Delivery from B5, therefore being referenced in cell A2 since it's concatenated and it should go into the same spreadsheet but tab xyz and go to cell A2 and then column and cell B2 and give me the date of 1/12/18 from that cell. I want it to do this everytime I changed the category. So, in other words, if I select purchase instead of delivery i want it to give the date 2/10/18 on cell C5.

 

[HackSlash]

Sounds like you need a database backend and a real GUI program for the front end. There are systems like the one you describe that are coded over the course of years by a team of people. You just buy it and import your xl data in to the database.

 

[jtakw]

Hi,

Is this what you mean?

Book1
	A	B	C
1
2	Not Used in formula
3
4	EMPLOYEE	CATEGORY	DATES
5	204Smith	Delivery	1/12/2018
6	123Johnson	Pick-up	2/3/2018
7	204Smith	Pick-up
8
9	123Johnson	Purchase
10	204Smith	Purchase	2/10/2018
11	123Johnson	Delivery	2/2/2018
Sheet1

Cell Formulas
Range	Formula
C5	=IF(OR(A5="",B5="",SUMPRODUCT((XYZ!A$2:A$10=A5)*(XYZ!B$1:D$1=B5)*(XYZ!B$2:D$10))=0),"",SUMPRODUCT((XYZ!A$2:A$10=A5)*(XYZ!B$1:D$1=B5)*(XYZ!B$2:D$10)))

Book1
	A	B	C	D
1	Employee	Delivery	Pick-Up	Purchase
2	204Smith	1/12/2018		2/10/2018
3	123Johnson	2/2/2018	2/3/2018
XYZ

 

[samyscraps01]

What does this mean? =IF(OR(A5="",B5="", are those blanks? ""

 

[jtakw]

What does this mean? =IF(OR(A5="",B5="", are those blanks? ""

The formula checks to see if there's any value in A5, B5, and corresponding cells in XYZ tab, if Not, results Blank, otherwise, continue formula.

 

[samyscraps01]

I got it!

=IF(OR($A$5="",$B$5="",SUMPRODUCT(('Raw Data'!$A$2:$A$29=$A$5)*('Raw Data'!$B$1:$O$1=$B$5)+($A$5="ALL")*('Raw Data'!$B$2:$O$29))=0),"",SUMPRODUCT(('Raw Data'!$A$2:$A$29=$A$5)*('Raw Data'!$B$1:$O$1=$B$5)*('Raw Data'!$B$2:$O$29)))

Notice that I added the section in red font. How can I get this as an array? As an example in the drop-down menu if I select the name of the person and in the next drop-down i select the category. I want to be able to select all and have it show me all the categories for that person which means it'll populate all the dates that correspond to each category for that particular person that I have selected on the drop-down. Does that make sense?