Change how excel auto adjusts formula down a column

Copper Contributor

I'm creating a workbook that calculates power demands when appliances are switched on throughout different hours of the day. I've used this formula to gather data from two other sheets and insert a specific value into each column:

Question.PNG

Focusing on column D, when I drag this cell down to 27, the formula copies and adjusts to reference the next cell in 'Load Schedules' and in 'Load Ratings'. I want this to happen for 'Load Schedules', but I want 'Load Ratings' to be read from the same cell (B4) for every column in this sheet. I've manually adjusted them for the first two columns, but this takes ages. Is there a faster way by changing how excel autofills and adjusts the formula?

 

1 Reply

@willgordge You just need to make 'Load Ratings'!B4 as absolute reference using dollar sign in-front of column and row of B4. Try below formula-

=IF('Load Schedulers'!D3="On",'Load Ratings'!$B$4,0)