Excel – How to drag the Excel formula horizontally across and vertically down, while keeping a relative reference

microsoft excel

See sample table

In the sheet (linked image aboveI've written the formula such that an "X" is inserted across my timeline for tasks that start and finish with the timeline column headers. The formula I've written is:

=IF(AND(G$2>=$C4,G$2<=$D4),"X","")

What I'd like to do is to replace the "X" with the "Avg hrs/Day" (column F).

I realized that while tweaking the formula in G4 as an example, I can't just reference cell F4 and drag it all the the way to the right and then down at the same time.

How can I accomplish this? This is just a sample table. The real table I have to apply this on has about 75 columns of dates in the timeline and 200 rows of tasks so I cannot change the relative reference row-by-row.

Best Answer

Looks like the sample formula you provided is for cell G4. Can't you reference the Avg hrs/Day just like you reference the Start and Finish dates in columns C and D?

=IF(AND(G$2>=$C4,G$2<=$D4),$F4,"")

Like LPChip said in the comments above, put this formula in cell G4. Drag it all the way to the right, then release, then drag the new selection all the way down.

Related Question