Cpying the formula accross all columns keeping the reference column but chancging rownumbr

[ciapul12]

Hi ALL
How can I copy this formula across to all columns keeping the source column always B but changing the row number when copying across?

=IF(Index!B4=1,"Team briefing required",IF(Index!B4=2,"Full Re-Training of the procedure required",""))

Thank you.

[etaf]

if you copy across the row number will not change
but a $ in front of the column letter , will stop the column changing as you copy across

=IF(Index!$B4=1,"Team briefing required",IF(Index!$B4=2,"Full Re-Training of the procedure required",""))

the row will change if you copy down

so assuming the formula is in A1 and you copy to E1
then it will still read index!$B4=1

did you want the row number to change from a 4
to 8
as its moved from A to E

and also the B to change
to F

[Pete_UK]

Try it like this:

=IF(INDIRECT("'Index'!B"&COLUMNS($A:A)+3)=1,"Team briefing required",IF(INDIRECT("'Index'!B"&COLUMNS($A:A)+3)=2,"Full Re-Training of the procedure required",""))

Hope this helps.

Pete

[RobertMika]

=IF(INDEX(index!$B$4:$B$100,COLUMN(A1))=1,"Team briefing required",IF(INDEX(index!$B$4:$B$100,COLUMN(A1))=2,"Full Re-Training of the procedure required",""))

[bebo021999]

Try to replace Index!B4 with:

INDIRECT("Index!B"&COLUMN(D:D))

in your formula.

[ciapul12]

=IF(INDEX(index!$B$4:$B$100,COLUMN(A1))=1,"Team briefing required",IF(INDEX(index!$B$4:$B$100,COLUMN(A1))=2,"Full Re-Training of the procedure required",""))

Hi Robert
I have used your solution and it works but I'm having an issues with the formula as it doesnt put the text into last 2 columns.
This is a sample workbook only but I am getting the same problem in my original workbook.
Can you tell me what I am doing wrong here please?
Thank you for your help :-)
Dan

[RobertMika]

COLUMN(C7) returns 3 as C us the 3rd letter of the alphabet so your formula starts from 3rd value in Column B of the Index sheet(WOPR2203 Procedure3 v4 20022015)
Change that to
=IF(INDEX(Index!$B$2:$B$48,COLUMN(A7))=1,"Team briefing required",IF(INDEX(Index!$B$2:$B$48,COLUMN(A7))=2,"Full Re-Training of the procedure required",""))
to return first and the subsequent values.

[ciapul12]

COLUMN(C7) returns 3 as C us the 3rd letter of the alphabet so your formula starts from 3rd value in Column B of the Index sheet(WOPR2203 Procedure3 v4 20022015)
Change that to
=IF(INDEX(Index!$B$2:$B$48,COLUMN(A7))=1,"Team briefing required",IF(INDEX(Index!$B$2:$B$48,COLUMN(A7))=2,"Full Re-Training of the procedure required",""))
to return first and the subsequent values.

Hi Robert
Thank you for your help.
This has worked like a charm
Dan