Excel gurus - Part II

[Desmo]

Need a little help with a formula I've got. It's currently working but I need to extend the range of cells covered but when I change the figure it stops working and I can't figure out why.

On the sheet JMA I have a list of jobs I do showing job no., customer name, job type and cost. The sheet this formula is on is for my invoice. I just have to add an invoice number to the job on the JMA sheet and it adds it to the current invoice sheet automatically.

When I first got the formula working, I only covered the first 100 rows on the JMA sheet, I now need to extend this to cover more cells.

The current formula that works is...

=IF(ISERROR(INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$ 1:$A$100=$C$3,ROW(JMA!$A$1:$A$100)),ROW(1:1)),4)), "",INDEX(JMA!$A$1:$F$100,SMALL(IF(JMA!$A$1:$A$100= $C$3,ROW($A$1:$A$100)),ROW(1:1)),4))

...but if I change every reference of 100 to 200 it stops working and I can't figure out why. The cell just goes blank even though it showed data before and I'm still covering the same range of cells, just more of them.

[kaiowas]

Looks like (although I can't be sure) part of the formula uses an array formula. When entering these after you have made your changes you have to hold down ctrl and shift whilst pressing enter.

Also there may be a minor error in the formula itself, in the first argument for the main IF function the ROW function references 'JMA!$A$1:$A$100' but in the corresponding part where you recalculate this value later in the formula the worksheet name hasn't been included.

[Desmo]

Originally Posted by kaiowas

When entering these after you have made your changes you have to hold down ctrl and shift whilst pressing enter.

God damn it boy you were right. I had this niggling feeling it was something like that earlier but tried holding ctrl+enter and shift+enter but no joy. If only I'd tried all three

Cheers Phil