Auto population issues!!!

[DHOFERER]

Hi all,

I am trying to create a formula that will automatically pull someone's name from a list depending on what code is listed under a specific day. The catch is the formula result has to be consider the day of the week and the time of day (AM or PM) that is selected in another cell.

For example:

If "Danielle" is listed in cell A2 on Tab 1, and below "Wednesday" (E4) the code "D1" appears in cell E5. On a separate page I would like for "Danielle" to appear in the cell next to where "D1" appears. This cell on Tab 2 is dependent on another cell that can toggle between "AM" and "PM" (AM = "L1", PM = "D1"). Lastly, the day of the week listed is determined by the function =TODAY().

TAB 1

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]MONDAY[/TD]
[TD]TUESDAY[/TD]
[TD]WEDNESDAY[/TD]
[TD]THURSDAY[/TD]
[TD]FRIDAY[/TD]
[TD]SATURDAY[/TD]
[TD]SUNDAY[/TD]
[/TR]
[TR]
[TD]DANIELLE[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]L1[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OFF[/TD]
[TD]D1[/TD]
[TD]D1[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD]D2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]L1
[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D2[/TD]
[TD]D1[/TD]
[TD]D4[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</tbody>[/TABLE]


TAB 2 IF A2 = PM

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]WEDNESDAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]SERVER[/TD]
[/TR]
[TR]
[TD]D1
[/TD]
[TD]DANIELLE[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]PAUL[/TD]
[/TR]
</tbody>[/TABLE]

TAB 2 IF A2 = AM

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]WEDNESDAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]SERVER[/TD]
[/TR]
[TR]
[TD]L1[/TD]
[TD]DANIELLE[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am confident a formula exists outside of writing the longest nested IF function known to man. Maybe something to do with using HLOOKUP and VLOOKUP in combination with each other? CAN SOMEONE PLEASE HELP ME!!!

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

I think a bit much is still unknown but see if this is headed in the right direction.

IF all of these things happen ..

.. the day of the week listed is determined by the function =TODAY().

1. The above refers to cell A1 of TAB 2 and returns a text value [eg the formula might be =UPPER(TEXT(TODAY(),"dddd")) ]


2. The D1, L1, D3 etc codes can appear at most once in any column on TAB 1

.. then you could try this in cell B3, copied down.

Excel 2010 32 bit
	A	B
1	WEDNESDAY
2	PM	SERVER
3	D1	DANIELLE
4	D2	PAUL
TAB 2

Cell Formulas
Range	Formula
B3	=IFERROR(LOOKUP("ZZ",'TAB 1'!A$1:INDEX('TAB 1'!A$1:A$10,MATCH(A3,INDEX('TAB 1'!B$1:H$9,0,MATCH(A$1,'TAB 1'!B$1:H$1,0)),0))),"")

Excel 2010 32 bit
	A	B	C	D	E	F	G	H
1		MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
2	DANIELLE	OFF	OFF	L1	OFF	OFF	OFF	OFF
3		OFF	D1	D1	D3	D4	D2
4	PAUL	L1	OFF	OFF	OFF	OFF	OFF	OFF
5		D2	D3	D2	D1	D4	OFF	OFF
TAB 1