# Require Cells Between Two Values to be Filled with "1"

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Hi I would like some assistance regarding the following

Where A1 will equal "1" and A15 will equal "1" - I require every cell in between the two to be filled with a "1".

My aim is to apply this to a small base of data and to conditional format the "1" so that at a glance I can sum the total amounts of rows and associated 'blocks'

• Re: Require Cells Between Two Values to be Filled with &quot;1&quot;

Attached the excel file; I am using this as a tool to check shifts on a roster

• Re: Require Cells Between Two Values to be Filled with &quot;1&quot;

put this in A2 and drag till A14

=IF(AND(\$A\$1=1,\$A\$15=1),1,0)

• Re: Require Cells Between Two Values to be Filled with &quot;1&quot;

Thanks very much for the prompt assistance, I can't upload the file so will have to explain in more detail

I have in C1; The time of day in 15 minute blocks (0015/0030/0045 etc)
I have in column A/B; shift start times and finish times plotted against the time data in row 1 using an IF formula to return a "1" where the shift time matches the top row

What I would like to achieve is for every instance where there is a "1" followed by blanks and the closing "1" - I would like to fill the values between the two values with more "1's"

Example Row 1: 0500/0515/0530/0545/0600
Example Column A:B: 0500 start and 0600 finish
Example Matched Result: 1,blank,blank,blank, 1
Required Outcome: 1,1,1,1,1

Outside of where there is a genuine blank cell - I would like this to remain blank, only fill cells between two "1" values. Its a challenging one!

• Re: Require Cells Between Two Values to be Filled with &quot;1&quot;

use this

where A2 has start date B2 has end date and from C2 paste this formula =IF(AND(\$A\$2<=C1,\$B\$2>=C1),1,0)

• Re: Require Cells Between Two Values to be Filled with &quot;1&quot;

So I have managed to upload the file - which I hope will help solve the challenge I am facing!

I hope that the actual data makes finding a solution easier, all I really want is for the 1's that are on the same row to be linked by a colour (refer attachment example). Was under the impression that I needed conditional formatting - but this doesn't exactly work!