Copying across formula using $

[jamblo]

I'm struggling to work out which is the correct way to use the $ symbol to 'pin' a cell.

In the attached sheet, I have a set of questions in column A with preset answers in B.

If the answer is "Yes", this should then let column G go ahead.

I.e. if B3="Yes", G3:G16 will work. That is ok.

The next question answer in B4 should apply to H3:H16

However I'm at a loss when it comes to dragging across and down as I can't get the logical test to change to the correct value.
E.g. H3 should say =IF(B4="yes",$P$5,0)

Thanks in advance,

[josephteh]

Try this:=IF(INDEX($B$3:$B$9,COLUMNS($G$2:G$2))="Yes",$P$5,0), copy down and across.

[jamblo]

Thanks, works well