Dynamic Array solution for a Gantt Chart

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hey everyone!

I wanted to share a little situation I am dealing with.

So I have been using dynamic arrays for some time now and I gotta say they are indeed amazing!

CJewrhK.png


Right now I am trying to make my Gantt Chart more dynamic, and I am stuck with the following problem.
So I need to generate an array of 1's and 0's (ones and zeroes) that check whether a calendar date is between the Start ("Inicio" sorry for the spanish in the sheet), and End ("Final").
So if the date on row 19 is indeed between the Inicio and Final, then output a 1, if not a 0.
You can see the formula I am using, however it is not dynamically adjusting. I have tried to use the M7 reference as M7# and it doesn't work either.

Any other ideas on how to solve this?
Maybe using a matrix function like MMULT?
Or something else?
Any ideas?

Any and all help will be deeply appreciate it :)

Have a great one!


ORLANDO
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,459
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOP
101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/2021
201/01/202104/01/20211111000000000
306/01/202109/01/20210000011110000
4
Main
Cell Formulas
RangeFormula
D2:P3D2=(B2<=$D$1:$P$1)*(C2>=$D$1:$P$1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:P3Expression=D2=1textNO
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
Further to @Fluff suggestion, please check the below, if you a require dynamic formula that accommodates both columns and rows (you just enter it once without dragging):

Gantt Chart.xlsx
ABCDEFGHIJKLMNOPQR
101-Apr-2102-Apr-2103-Apr-2104-Apr-2105-Apr-2106-Apr-2107-Apr-2108-Apr-2109-Apr-2110-Apr-2111-Apr-2112-Apr-2113-Apr-2114-Apr-2115-Apr-2116-Apr-21
203-Apr-2107-Apr-210011111000000000
306-Apr-2113-Apr-210000011111111000
403-Apr-2109-Apr-210011111110000000
513-Apr-2116-Apr-210000000000001111
Sheet1 (2)
Cell Formulas
RangeFormula
C2:R5C2=(FILTER($A:$A,ISNUMBER($A:$A))<=FILTER($1:$1,ISNUMBER($1:$1)))*(FILTER($1:$1,ISNUMBER($1:$1))<=FILTER($B:$B,ISNUMBER($B:$B)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:R5Cell Value=0textNO
C2:R5Cell Value=1textNO


Regards
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,459
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff 1.xlsm
ABCDEFGHIJKLMNOP
101/01/202102/01/202103/01/202104/01/202105/01/202106/01/202107/01/202108/01/202109/01/202110/01/202111/01/202112/01/202113/01/2021
201/01/202104/01/20211111000000000
306/01/202109/01/20210000011110000
404/01/202107/01/20210001111000000
508/01/202115/01/20210000000111111
603/01/20210000000000000
705/01/202110/01/20210000111111000
80000000000000
90000000000000
100000000000000
Main
Cell Formulas
RangeFormula
D2:P10D2=(B2:B10<=$D$1:$P$1)*(C2:C10>=$D$1:$P$1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:P10Expression=D2=1textNO
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,459
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top