Can you do an IF(AND array

[Goliath1234]

Hi,

I am trying to create a calendar from a list of activities where if the cell is between a certain time frame it will output the name of the activity during that time frame. The name of cells are within three cells A5, B5 and C5 which the text will be combined into the one cell. However i currently have this set to one specific row 5 (1 activity). I would like this to pick up an array of rows (multiple activities) but the AND function wont let me do a formula with an array. See below formula.

=IF(AND(K25>$D$5,K25<$F$5),$A$5 &" " & $B$5 & " " &$C$5," ")

i have attempted the below but get a #VALUE ! error
=IF(AND(K25>$D5:$D8,K25<$F5:$F8),$A5:$A8 &" " & $B5:$B8 & " " &$C5:$C8," ")

Is there a way around this?

Thanks

 

[James006]

Hi,

Most probably you will need an Array Formula ...

Could you describe your worksheet ... and give an example of your expected result ...

 

[Goliath1234]

Hi,

Most probably you will need an Array Formula ...

Could you describe your worksheet ... and give an example of your expected result ...

Hi James,

Data from columns A-G are shown in table below;

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl65, width: 145"]Activity[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl65, width: 117"]Supplier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"]Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Time in[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Date In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Time Out[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Date out[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD]Flight[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD]Qatar Airways[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD]Sydney to Doha[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="align: right"]21:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]18/02/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="align: right"]05:20:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="align: right"]19/02/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The aim in these columns is to produce a calendar style format next to it. Text from A,B,C get put into one box when the time is between the selected times in Column D & F. See below.

[TABLE="width: 226"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]18 February 2019[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20:00:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]21:00:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]22:00:00[/TD]
[TD]Flight Qatar Airways Sydney to Doha[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 226"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]23:00:00[/TD]
[TD]Flight Qatar Airways Sydney to Doha[/TD]
[/TR]
[TR]
[TD="align: right"]00:00:00[/TD]
[TD]Flight Qatar Airways Sydney to Doha[/TD]
[/TR]
</tbody>[/TABLE]

The problem with this is i can't get an array formuyla to work with a selection of columns.

 

[James006]

Hi,

In order to make your life easier, my recommendation would be :

1. To use Sheet 1 for all your Data

2. To build your Calendar in Sheet 2

3. To use a macro to automate and speed up your process

Hope this will help

 

[Goliath1234]

Hi James,

Could you please clarify how you would apply this to a macro?

Thanks