Copy Formula Without It Changing? (1 Viewer)

[.Justin]

I currently have the following formula which works fine.

=COUNTIFS('Patient Summary'!B6:B454,"6/7/2016",'Patient Summary'!C6:C454,">=00:00",'Patient Summary'!C6:C454,"<01:00")

However when I copy the formula into a row below to do a different date it changes the code for the rows to look a which I do not want it to do. Is there anyway I can stop this happening?

=COUNTIFS('Patient Summary'![B]B9:B457[/B],"6/7/2016",'Patient Summary'!C9:C457,">=00:00",'Patient Summary'![B]C9:C457[/B],"<01:00")

Also for the dates is there anyway I can get this to take the date value from a cell rather than having to change the date manually in the formula?

 

[CJ_London]

this seems to be too simple an answer but you use the $ sign to fix one or the other or both dimensions. e.g.

=$A1 in cell b2 will remain the same if copied to b3
=$A$1 in cell b2 will remain the same when it is copied but

...B$6:B454... when copied to the next row below will become

....B$6:B455....

Not sure which bit you don't want to change

when the cursor is on the particular cell reference you can use the F4 key to cycle through the options

 

[.Justin]

this seems to be too simple an answer but you use the $ sign to fix one or the other or both dimensions. e.g.

=$A1 in cell b2 will remain the same if copied to b3
=$A$1 in cell b2 will remain the same when it is copied but

...B$6:B454... when copied to the next row below will become

....B$6:B455....

Not sure which bit you don't want to change

when the cursor is on the particular cell reference you can use the F4 key to cycle through the options

Thanks CJ. The cell references should always remain the same the only thing that should change on each row is the date. Hopefully this makes more sense:

 

[NBVC]

As mentioned by CJ_London, you need to make the reference ranges absolute if you don't want them to change, by adding the $ before the column and/or row references.

Also, instead of hard-coding the dates and times, you can reference them in the formula, using partial absoluting (i.e. only apply $ before column for date so that date reference doesn't change as you copy across, but does change as you copy down, and apply $ before row reference for times so that time doesn't change as you copy down, but does change as you copy across.

Try:

=COUNTIFS('Patient Summary'!$B$6:$B$454,$B2,'Patient Summary'!$C$6:$C$454,">="&C$1,'Patient Summary'!$C$6:$C$454,"<" & C$1+1/24)

where B2 is the cell with first date in your attached table row headers, and C1 is the cell with first time in your table column headers.

 

[CJ_London]

The cell references should always remain the same the only thing that should change on each row is the date. Hopefully this makes more sense:

I have provided the answer in my post - what don't you understand?

 

[GohDiamond]

You could name your range of dates B6:B454 as PatientDates and you range of TimeElapsed C6:C454 as PatientConsultTime. Once you have the alias naming convention set up it will always represent that range no matter where you drag the formula to. Then name the Date Cell as SelectedDate.

=COUNTIFS('Patient Summary'!PatientDates,SelectedDate,'Patient Summary'!PatientTimeConsult,">=00:00",'Patient Summary'!PatientTimeConsult,"<01:00")

Cheers!
Goh

 

[NBVC]

If you are using Named Ranges, then generally you would not need to reference the worksheet also... (unless you changed the scope of then named range from the default Workbook)