Fill across with formulas using INDIRECT?

Hello, I am new to the forum and I hope you can help...

I am trying to fill across a formula - =IF(ISBLANK(M4),"",IF(M4=6,0,IF(M4=5,1,IF(M4=4,2,IF(M4=2,4,IF(M4=1,5,IF(M4=0,6)))))))

However when filling across, I need it to change the row number instead of the column. My search on the web has suggested I use INDIRECT, but I am not sure how to apply it to my spreadsheet (attached)...
Book2.xls

Could someone explain please? with instructions to suit a beginner?

Many thanks!

First, let's simplify that formula a bit...

Try
=IF(M4="","",6-M4)

Now to make the row incriment when filling accross, add in the offset and columns functions.
=IF(OFFSET($M$4,COLUMNS($A$1:A$1)-1,0)="","",6-OFFSET($M$4,COLUMNS($A$1:A$1)-1,0))

DO NOT adjust the $A$1:A$1 References, they are not related to your data.

Or, if you want a non-volatile formula:

=IF(INDEX($M:$M,COLUMNS($A:A)+3)="","",6-INDEX($M:$M,COLUMNS($A:A)+3)

then copy across.

Hope this helps.

Pete

Originally Posted by Jonmo1

First, let's simplify that formula a bit...

Try
=IF(M4="","",6-M4)

Now to make the row incriment when filling accross, add in the offset and columns functions.
=IF(OFFSET($M$4,COLUMNS($A$1:A$1)-1,0)="","",6-OFFSET($M$4,COLUMNS($A$1:A$1)-1,0))

DO NOT adjust the $A$1:A$1 References, they are not related to your data.

Many thanks for this... although I don't understand it, it works and I can move on...

Originally Posted by ohnomis

I'm getting errors with this one, but probably user error not the formula

Yeah, sorry, there should be another bracket at the end.

=IF(INDEX($M:$M,COLUMNS($A:A)+3)="","",6-INDEX($M:$M,COLUMNS($A:A)+3))

Glad you got it working.

Pete

Originally Posted by Pete_UK

Yeah, sorry, there should be another bracket at the end.

=IF(INDEX($M:$M,COLUMNS($A:A)+3)="","",6-INDEX($M:$M,COLUMNS($A:A)+3))

Glad you got it working.

Pete

Ah... great, thanks Pete.

Out of interest, what if I wanted to fill down, and change the column instead of row... how would the formula look?

Well, you would have a row reference instead of $M:$M and you would use ROWS($1:1)+start_number instead of the COLUMNS term.

Hope this helps.

Pete